Distributing Sum in Excel

shakeydude · 29-03-2010 09:56PM #1

Hi,

I was hoping for some pointers in Excel. I have a sum, say 30 that I want to distribute throughout 24 hours in a day. Is there a specific function in excel that can distribute normally for instance but with the mean at mid-day. I basically want the sum to reach 30 each day but to control the distribution.

Sorry if this is unbelievably basic, but all help is appreciated.

Shakeydude:)

Fremen · 30-03-2010 06:04AM

Let me see if I've understood what you want to do. You want to create say 24 random variables X_1 to X_24 such that each random variable is normally distributed, and

[latex] \displaystyle \sum_{n=1}^{24} X_n = 30 [/latex]

right? You can do this with something known as a Brownian bridge. I'll have to look up the distribution, so let me know if this is what you want to do.

You should note that you can't have the X_n's mutually independent *and* pick the mean and variance. If you want each X_i to have a given mean and variance, you have to give up the assumption that the X_i's are independent.

bnt · 30-03-2010 01:08PM

I don't think you'll find an exact formula, since the Normal distribution is continuous and unbounded i.e. its sum is normally 1 when integrated from -inf to +inf. (You want it to sum to a value within a bounded range.) You can approximate it, though. I threw together a quick spreadsheet that does that (attached). It has a "width" parameter so you can change the distribution, and an arbitrary "n" parameter to scale the results to what you want.

How did I get it to add up to exactly 30? "Goal Seek" on "n". :pac:

edit: it looks the most "normal" with width = 30, n = 3.093938083

shakeydude · 30-03-2010 10:54PM

Thanks Fremen and bnt,

You have given me some good ideas. I think the integration of the sum to a specific function should give me some joy to my problem.

From your PsuedoDist spreadsheet I dont think that the load will be normal but it will follow a specific pattern throughout the day. (The sum has already been normally distributed and the daily pattern will be random and add to the sum)

Sorry if my thoughts are scattered, I am trying to get my head around it

Shakeydude

Distributing Sum in Excel

Comments