Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Random Function
"PAL" wrote:
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 ----- "PAL" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random function - weighted | Excel Worksheet Functions | |||
Random numbers function | Excel Worksheet Functions | |||
random number function | Excel Worksheet Functions | |||
VBA "Rnd" Function: Truly Random? | Excel Discussion (Misc queries) | |||
How can I use the random function? | Excel Worksheet Functions |