Problem with "=rand()"

**comotoman** · 09-20-2005, 10:39 AM

I wrote if staements for a random number generator, yet they dont work with the random number generator. All I am trying to do is make sure two of the same numbers do not show up. Without the rand. the formulas work fine. HELP!!!!

**swatsp0p** · 09-20-2005, 10:58 AM

post your formulas so they can be evaluated. what is the expected result? what are you getting as a result?

more info, please.

**comotoman** · 09-20-2005, 02:24 PM

=rand()*(100-1)+1 Used in 5 cells. Works fine.

=if(a1=b1,"Repeat","Good") cell 1
=if(a1=c1,"Repeat","Good") cell 2
and so on, used to see if any of the first 5 cells repeats itself.
The above if formulas work when i test the cells without Rand, but do not work when i enter the rand formula from above.

**swatsp0p** · 09-20-2005, 03:02 PM

All seems to work when I use your formulas. There are ten possible combinations to check for repeats (note that RAND() recalculates with every change to the sheet)

If your RAND formula (=rand()*(100-1)+1) is in cells A1:E1 and in A2:A11 you have 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")

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()*(100-1)+1).

What is failing for you?

**comotoman** · 09-20-2005, 03:17 PM

It would seem that it does not recalculate after each change. By reducing the range to 10, and using whole numbers, they duplicate more often. In doing this, the results never change, and this is the root of my problem.

**swatsp0p** · 09-20-2005, 03:58 PM

Are you saying that instead of =rand()*(100-1)+1 your formula is actually =rand()*(10-1)+1?

Do you have calculation set to automatic (Tools>Options>Calculation tab)? Pressing F9 should cause your sheet to recalc. and generate a new set of random numbers in your range.

Are you trying to make it so that no duplicates EVER happen or simply to flag when they do?

**comotoman** · 09-20-2005, 04:21 PM

I just reduced the rand size to increase the duplication, and i would like to show either no duplicates ever, or flag me when there is a duplicate. Currently Im just trying to flag.

JE McGimpsey · 09-20-2005, 05:05 PM

It's hard to tell what you're doing since you didn't include the
formulae, but this might be of help:

http://www.mcgimpsey.com/excel/randint.html

In article <comotoman.1vnxmh_1127228714.5956@excelforum-nospam.com>,
comotoman <comotoman.1vnxmh_1127228714.5956@excelforum-nospam.com>
wrote:

> I wrote if staements for a random number generator, yet they dont work
> with the random number generator. All I am trying to do is make sure
> two of the same numbers do not show up. Without the rand. the formulas
> work fine. HELP!!!!

**comotoman** · 09-21-2005, 10:14 AM

The formulas are as posted above, all I really need to do is flag if [=rand()*(100-1)+1] in 5 cells has any duplicates.

**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)