Problem with "=rand()"

[comotoman]

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]

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

more info, please.

 

[comotoman]

=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 d
not work when i enter the rand formula from above

 

[swatsp0p]

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]

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

 

[swatsp0p]

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

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

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

 

[JE McGimpsey]

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

 

[comotoman]

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.

 

[comotoman]

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]

Anybody have any ideas?

 

[Mike Middleton]

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]

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]

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)

 

[comotoman]

That will teach me to be more specific with my questions, it works like
a charm. Thanks

 

[swatsp0p]

I am so glad we got this all sorted out. Details are vital to getting
the correct answer to a problem.

Cheers!