Results 1 to 2 of 2

Thread: Probability Distributions in Excel VBA

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Probability Distributions in Excel VBA

    Hey all,

    I'm in need of a pretty big helping hand here I think.

    I've been trying to figure out how to make a certain number of plant buds open per day, based on a normal distribution. In order to aid my bad explanation, and for entertainment purposes, I've added a picture to illustrate:

    Name:  The Italian (Paint) Job.jpg
Views: 795
Size:  11.9 KB

    So, what should happen is: Each plant has a lot (hundreds) of buds, and a unique day of peak budding. The percentage of buds that open each day should be a normal distribution about the peak budding day, starting to open maybe 20 days before the peak day. However, my lack of experience with Excel VBA worksheet functions and (sadly) maths in general has really been throwing me for a loop over the last few days.

    The end goal would be a subroutine where every day a dice is rolled to decide how many buds open. In this case, the first buds open around the 20th, the most open on the 40th, and all are open by around the 60th day. So maybe on day 20, 1% of buds open, on the peak day, 10% open, and then back down to 1%. The total # buds are stored in a 2D array, and when they open, they become flowers, which are stored in another column of the array.

    I've been working with the inverse normal function already, but this seems like it is a little more complex, of course I could just be over-complicating things.

    I'd really appreciate a hand if anyone has one to offer.

    Cheers!

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Probability Distributions in Excel VBA

    use rnd in combination with other bits of math to get the center of distribution.
    Then another rnd to how many open that day.
    Recursive loop dividing whats left, days spaced between left/right and the center, reduce until <x buds are left and pop them on the last day...

    eg.. 100 buds
    (rnd*20)+20 <<< to start at day 20 and 'randomly' pick a day with in 20 days ie 20-40 days lets say 30...
    %to open each day - say 30%?
    day 30 - 30
    day 29/31 - (100 - 30) / 2 %to open --> 70/2 = 35 -- 30% = 15
    day 28/32 - (100 - 30-30)/2 %to open -->40/2 = 20 -- 30% = 6
    etc....

    Maths bit you'll need to work out how you are going to do in to make it look better, but that's just an idea...
    The recusrive loop part will give you a headache --- good luck

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width