Problem with "=rand()"

**comotoman** · 09-21-2005, 02:06 PM

Anybody have any ideas?

Mike Middleton · 09-21-2005, 07:05 PM

comotoman -

> Anybody have any ideas? <

Please be more specific.

The RAND worksheet function returns numbers with 15 significant digits.

Your formula "=rand()*(100-1)+1" will also have 15 significant digits.

If you examine a sequence of several hundred or several thousand or several
hundred thousand, you will not see any duplicates.

> The above if formulas work when i test the cells without Rand, but do not
> work when i enter the rand formula from above. <

What do you mean by "do not work"?

> It would seem that it does not recalculate after each change. <

After each change of what? That is, what are you changing?

- Mike
www.mikemiddleton.com

**comotoman** · 09-22-2005, 01:53 PM

I hope this may help explain my problem.

(=rand()*(10-1)+1) are in cells A1:E1 and in A2:A11 the following formulas:

=IF(A1=B1,"Repeat","Good")
=IF(A1=C1,"Repeat","Good")
=IF(A1=D1,"Repeat","Good")
=IF(A1=E1,"Repeat","Good")
=IF(B1=C1,"Repeat","Good")
=IF(B1=D1,"Repeat","Good")
=IF(B1=E1,"Repeat","Good")
=IF(C1=D1,"Repeat","Good")
=IF(C1=E1,"Repeat","Good")
=IF(D1=E1,"Repeat","Good")

(I have changed the original formula from 100 to 10, thus allowing more duplicates as to test the sheet.)

cells A1:E1 are formated as "number" with "no decimal places".

When I update (F9) or change the sheet (type a #1 in a unassociated cell, pressing enter) to change the random #'s in cells A:1 thru E:1, and 2 duplicate whole numbers appear, the formulas in A2:A11 do not show "repeat", instead the results still show "good".

**swatsp0p** · 09-22-2005, 02:11 PM

Here is your problem... even though you have formatted as "no decimal places", the result of the RAND still has 15 decimals, you simply are suppressing the displaying of those digits.. As I indicated in an earlier post

Each cell in this range will return "Good" (as there are no duplicates), plus the odds of a repeat are staggering... unless you use the INT function =INT(rand()*(10-1)+1).

PLEASE try my suggestion: =INT(rand()*(10-1)+1)