-
Apr 5th, 2017, 08:01 AM
#1
Thread Starter
Member
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:
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!
-
Apr 6th, 2017, 11:58 AM
#2
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|