Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2
I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.
Last edited by JP2008; 02-10-2009 at 03:15 PM.
I think this worksI tried on 3000 cells and got
Please Login or Register to view this content.Does this work for you?Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hi, thanks for the formula.
The logic of the formula looks 100% correct but the cat & rabbit results come out the wrong way round. Its something to do with the random function which appears 3 times in the formula, perhaps Excel recalculates as it steps through the formula.
To test this I took the rand() function out of the calculation and put it into cell D1 so there could only be one random number in each row.
Changing the formula to
=IF(D1>0.5,"Dog",IF(AND(D1>0.2,D1<=0.5),"Cat","Rabbit"))
This does return 500 dog, 300 cat and 200 rabbit
Strange.Oh well, your method will work just fine.
um.. Yea it does work. the Rand() will return a different number every time it's called, so yes you need to call it only once. The trick of using the cell refference seems to work. Though I suppose there is a better way.
However, What I wanted to point out is one thing.
While that does work, it can be shortened.Please Login or Register to view this content.
If the D1 is greater than 0.5, you'll get dog. If it isn't, then we go to second if.Please Login or Register to view this content.
If D1 is greater than 0.2 we don't need to test if it's less than or equal to 0.5 cause that was done b y the first IF. So we just return Cat. And if it's not greater than 0.2 then return Rabbit.
=LOOKUP(RAND(), {0,0.5,0.8}, {"Dog","Cat","Rabbit"})
Entia non sunt multiplicanda sine necessitate
Nice Shg! I keep forgetting the lookup function. ::Smacks head::::
Hi. Sorry to dredge up an old thread.
I was looking to do exactly as the thread describes and found Shg's solution to work nicely. However...
...
How could I make this work by referencing cells instead of hard-coding them into the formula? I have quite a lengthy list of probabilities and outcomes!
Should work nicely with a VLOOKUP
=VLOOKUP(RAND(), $A$2:$B$10, 2)
with lower percentile limits in A2:A10 and results in B2:B10
Works very nicely.
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks