Random Function

[PAL]

I’m trying to create an algorithm that, upon entering Person’s ID and date,
will notify me if the individual has been randomly selected to receive a
gift. Each person should have a 20% chance of being selected.

A2 Cell: Any number
B2 Cell: Any date
C2 Cell: =IF(A2,IF(B2,E2,""),"")
E2 Cell: =IF(F2<=0.2,"Yes","No")
F2 Cell: =RAND( )

Problem: The RAND function updates with each entry, modifying the previously
entered records. Is there a way to prevent this from happening?

 

[JoeU2004]

Problem: The RAND function updates with each entry,
modifying the previously entered records.

And you don't think that's a wonderful feature?! Just kidding. Whoever
thought RAND and RANDBETWEEN should be volatile functions should have
his/her head examined. Probably some Lotus whiz kid a few decades ago :-(.

Is there a way to prevent this from happening?

I believe the only sure way to do this is with copy-and-paste-special-value.
You can overwrite the formula in F2; or you can put the RAND formula in some
out-of-the-way cell and copy-and-paste-special-value from there to F2.
Problem is: you will have to CAPSV every time you want to generate a new
random value.

Another alternative: use the following UDF (VBA):

Function myrand(Optional rng As Range) As Double
myrand = Rnd()
End Function

You can call it just like RAND:

=myrand()

Or you can pass a cell or range reference:

=myrand(A1)

The advantage of the latter is: every time A1 is modified, the latter
formula will generate a new random value. That can be useful.

Some caveats:

1. The VBA Rnd function probably has a shorter "period" than the Excel RAND
function. The "period" is the number of consecutive random values that can
be generated before repeating the sequence. On the other hand, the VBA Rnd
"period" is probably millions.

2. Your worksheet now has a macro. Well, duh! What I mean is: this might
encumber the use of the Excel file on other computers because many people
disable macros or don't know how to allow them.

3. Ctrl-alt-F9 will cause myrand() to be recalculated. That may or may not
be what you want.


----- original message -----

 

[MyVeryOwnSelf]

I’m trying to create an algorithm that, upon entering Person’s ID

and date, will notify me if the individual has been randomly selected
to receive a gift. Each person should have a 20% chance of being
selected.

A2 Cell: Any number
B2 Cell: Any date
C2 Cell: =IF(A2,IF(B2,E2,""),"")
E2 Cell: =IF(F2<=0.2,"Yes","No")
F2 Cell: =RAND( )

Problem: The RAND function updates with each entry, modifying the
previously entered records. Is there a way to prevent this from
happening?

One way is to use a circular reference. To allow circular references, use
Tools > Options > Calculation
and check the "Iterations" checkbox.

Start with column A all empty.

Use this in F2:
=IF(A2="",RAND(),F2)
This way, F2 freezes as soon as a number is entered in A2.

Caution: allowing circular references can be a risk. Later, if you make a
circular reference in the workbook by mistake, Excel won't flag it as an
error.