Thread: Random Function
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
MyVeryOwnSelf[_2_] MyVeryOwnSelf[_2_] is offline
external usenet poster
 
Posts: 143
Default 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.