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
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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

more info, please.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469176

=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.


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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.


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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 (ToolsOptionsCalculation
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?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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
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!!!!

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
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

Anybody have any ideas?


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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

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".


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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)


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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


--
comotoman
------------------------------------------------------------------------
comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292
View this thread: http://www.excelforum.com/showthread...hreadid=469176

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

Cheers!


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=469176