Random Function
Im trying to create an algorithm that, upon entering Persons 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.
|