Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Distributing Sum in Excel

  • 29-03-2010 8:56pm
    #1
    shakeydude
    Registered Users, Registered Users 2 Posts: 207 ✭✭


    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:)


Comments

  • #2
    Fremen
    Registered Users, Registered Users 2 Posts: 2,481 ✭✭✭Fremen


    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.


  • #3
    bnt
    Registered Users, Registered Users 2 Posts: 13,076 ✭✭✭✭bnt


    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.

    PsuedoDist.xls


    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

    You are the type of what the age is searching for, and what it is afraid it has found. I am so glad that you have never done anything, never carved a statue, or painted a picture, or produced anything outside of yourself! Life has been your art. You have set yourself to music. Your days are your sonnets.

    ―Oscar Wilde predicting Social Media, in The Picture of Dorian Gray



  • #4
    shakeydude
    Registered Users, Registered Users 2 Posts: 207 ✭✭shakeydude


    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


Advertisement