You are here:

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.  

Advanced Math

All Answers

Answers by Expert:

Ask Experts


randy patton


college mathematics, applied math, advanced calculus, complex analysis, linear and abstract algebra, probability theory, signal processing, undergraduate physics, physical oceanography


26 years as a professional scientist conducting academic quality research on mostly classified projects involving math/physics modeling and simulation, data analysis and signal processing, instrument development; often ocean related

J. Physical Oceanography, 1984 "A Numerical Model for Low-Frequency Equatorial Dynamics", with M. Cane

M.S. MIT Physical Oceanography, B.S. UC Berkeley Applied Math

Past/Present Clients
Also an Expert in Oceanography

©2016 All rights reserved.