Strange RAND results
I have just had some strange RAND results, which I have narrowed down to a simple repeatable case. Am I doing something wrong?
I have filled 20,000 rows with 3 columns. Column A and B contains =ROUND(RAND()*2,0)+1, to generate a random integer between 1 and 3. C1 contains =IF(A1=B1,1,0), C2 contains =IF(A2=B2,1,0), etc. Basically column C is set to 1 if the random numbers in A and B match, otherwise it is set to 0.
The average of columns A and B are close to 2, as expected. However, the average of column C is always around 37%, instead of the expected 33%.
Can anyone help with this please?
why not use =randbetween(1,3) this will give you your expected 33% dont know why the other doesnt as i agree it consistently gives around 37%(ah this is because its more likely to round to 2 than anything else)
if you try
=COUNTIF($A$1:$A$2000,1) and =COUNTIF($A$1:$A$2000,2) and =COUNTIF($A$1:$A$2000,3) you will see a disproportinate count of 2
Last edited by martindwilson; 05-08-2010 at 07:27 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
never heard of randbetween, but does exactly what I want, thanks. Just goes to show that RAND() should be used with care!
It's not the RAND() that's causing the 'odd' results, it's the ROUND, which reduces probability of the endpoints. You're generating a random number from 0 to 2. After the round,
0.0 to 0.5 returns 0 (25% probability)
0.5 to 1.5 returns 1 (50% probability)
1.5 to 2.0 returns 2 (25% probability)
RANDBETWEEN is the simple solution, but if you wanted to use RAND,
=INT(RAND() * 3) + 1
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks