Advanced Math/Distribution formula I think
QUESTION: What I am trying to do is distribute a number in an excel spreadsheet, I wrote a program that would do it in VBA but still am not sure what I did. I wrote a process that would take the number 1 thru x, sum it up then put 1 part in the first slot and 2 parts in the last slot, I got all the way to 1 part in the first up to 6 in the last, so I have 5 different routines to do that, I could not get any further but would like to get to 10 if possible.
I did not know what I was doing I just figured out a routine around what was happening and wrote a program around it. After further reading I think it is a combination of a summation formula and a distribution formula, but when I look them up it all greek to me.
Not sure if that is a good description of the question so I'll give you the answers and see if you can figure out what I did, sorry not a math major
If I want 1 part in the first slot and 2 in the last slot and I have 6 slots with 45 to divide up
.1111111 * 45 = 5
.1333333 * 45 = 6
.1555556 * 45 = 7
.1777778 * 45 = 8
.2 * 45 = 9
.2222222 * 45 = 10
Total 1 * 45 = 45
I picked 45 because it is the smallest number that comes out to whole numbers for this spread but it would work with and number.
Same thing with 1 to 3 into 6 intervals and 60 parts would be
.083333 * 60 = 5
.116667 * 60 = 7
.15 * 60 = 9
.183333 * 60 = 11
.216667 * 60 = 13
.25 * 60 = 15
Is there a way to make my cells the x's and y's and know the answer without running a program, and can it be done so it is a fraction like 1 to 1.5 or any thing I want to put in for a beginning and end.
ANSWER: I'm sure we can figure this out but I need to know what you mean by "I want 1 part in the first slot and 2 in the last slot". Do you mean the 1st digit in the decimal expression? Please clarify.
---------- FOLLOW-UP ----------
QUESTION: Another way to phrase it would be I know the beginning and ending answer I want 1 part of the whole number or summation in the first spot and 2 parts in the last spot, then the rest is divided evenly depending on how many spots or intervals there are. the decimals on the left are what I multiply by to figure out the part of the whole.
OK, I think I know what you're trying to do. The key was recognizing that when you say "parts" you really mean a multiple of the 1st increment. Thus, 2 parts really means 2 times the 1st part, where "part" is a fraction of the original number of interest. Whatever, here is my analysis.
Let X = number of interest (45 and 60 in your example)
N = number of intervals, slots, increments, that you want this number divided into (6 in both your examples)
m = multiple of 1st increment that gives the last increment (slot , interval,...) (2 and 3 in your examples)
From these parameters, we can calculate the terms, aj, in the sum you are after
X = sum[(j=1,N) aj ]
aN = m･a1
a1 = X/f
where f is the fraction of X represented by the 1st increment a1. It turns out that f is determined by the other parameters, so hold tight.
∆ = aN - a1 = m･a1 - a1 = a1･(m-1) = difference between 1st and last "parts"
δ = ∆/(N-1) = the amount (increment) each term in the sum increases with the index j
The sequence aj is given by
aj+1 = aj + δ the notation is a little screwy here in this typeset; j+1 is the index, I don't mean (aj) + 1
continuing this using the definition of aj gives
aj+1 = (aj-1 + δ) + δ = aj-1 + 2δ
until we get (with careful index management)
aj = a1 + (j-1)δ , so that
X = sum(j=1,N)[ a1 + (j-1)δ ]
= N(a1 - δ) + δ･sum[(j=1,N)j].
The sum at the end is N(N+1)/2. Plugging everything in, including the definition of a1 and δ, and solving for f we get
f = N[1 + (m-1)/2].
Using your values for N (= 6), m (= 2 and 3) and X (=45 and 60), reproduces the sequences in your examples.
I'm curious where this problem came from.