Strange RAND results

**tangent1** · 05-08-2010, 05:31 AM

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?

**martindwilson** · 05-08-2010, 06:51 AM

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

**tangent1** · 05-08-2010, 09:50 AM

never heard of randbetween, but does exactly what I want, thanks. Just goes to show that RAND() should be used with care!

**shg** · 05-08-2010, 11:23 AM

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