Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random only once
First, my experience: Some in VB programming in WORD and very basic
knowledge of Excel otherwise. Now the problem: We want to run random drug tests on employees for a client of ours. I've created a spreadsheet that uses the =INDEX(A2:A16,INT(RAND()*15)+1) Function I found here but there are two things I need to know if I can adjust with that. 1. Theoretically this could end up being a legal document when we pull names out of it and the employee contests the selection process. So we need to be able to "save" the results. Problem is, if you save this document, opening it automatically refreshes this formula and causes it to pick a new name. Any way to stop that? 2. Out of say, 30 names in a list, we would sometimes want to select 10 or 8 or 2 of them at a time for testing, so we don't want duplicates -- else I'd just put the formula in multiple cells. Is there any way to adjust the formula so that it does not duplicate itself? Or perhaps...better yet, have it post the results from each calculation into static cells elsewhere? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random only once
Regarding freezing the values that are used.....
Base a pivot table on the list of random values. Since the pivot table's cache is a snapshot of the source data, the pivot table will continue to work with those values until you referesh the PT data. Of course, the random value formulas will keep generating new numbers, but at least you'll be able work with a static list. For random numbers without duplicates, try this website: http://www.exceltip.com/st/Selecting...s%09/1110.html Does that help? *********** Regards, Ron XL2002, WinXP "Angyl" wrote: First, my experience: Some in VB programming in WORD and very basic knowledge of Excel otherwise. Now the problem: We want to run random drug tests on employees for a client of ours. I've created a spreadsheet that uses the =INDEX(A2:A16,INT(RAND()*15)+1) Function I found here but there are two things I need to know if I can adjust with that. 1. Theoretically this could end up being a legal document when we pull names out of it and the employee contests the selection process. So we need to be able to "save" the results. Problem is, if you save this document, opening it automatically refreshes this formula and causes it to pick a new name. Any way to stop that? 2. Out of say, 30 names in a list, we would sometimes want to select 10 or 8 or 2 of them at a time for testing, so we don't want duplicates -- else I'd just put the formula in multiple cells. Is there any way to adjust the formula so that it does not duplicate itself? Or perhaps...better yet, have it post the results from each calculation into static cells elsewhere? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random only once
Yup, that should do it, Thanks!
"Ron Coderre" wrote: Regarding freezing the values that are used..... Base a pivot table on the list of random values. Since the pivot table's cache is a snapshot of the source data, the pivot table will continue to work with those values until you referesh the PT data. Of course, the random value formulas will keep generating new numbers, but at least you'll be able work with a static list. For random numbers without duplicates, try this website: http://www.exceltip.com/st/Selecting...s%09/1110.html Does that help? *********** Regards, Ron XL2002, WinXP "Angyl" wrote: First, my experience: Some in VB programming in WORD and very basic knowledge of Excel otherwise. Now the problem: We want to run random drug tests on employees for a client of ours. I've created a spreadsheet that uses the =INDEX(A2:A16,INT(RAND()*15)+1) Function I found here but there are two things I need to know if I can adjust with that. 1. Theoretically this could end up being a legal document when we pull names out of it and the employee contests the selection process. So we need to be able to "save" the results. Problem is, if you save this document, opening it automatically refreshes this formula and causes it to pick a new name. Any way to stop that? 2. Out of say, 30 names in a list, we would sometimes want to select 10 or 8 or 2 of them at a time for testing, so we don't want duplicates -- else I'd just put the formula in multiple cells. Is there any way to adjust the formula so that it does not duplicate itself? Or perhaps...better yet, have it post the results from each calculation into static cells elsewhere? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random only once
Just change the calc mode of the sheet from auto to manual:
<Tools <Options <Calculation tab, And check "Manual". Now, to get a new random set, just hit <F9. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Angyl" wrote in message ... Yup, that should do it, Thanks! "Ron Coderre" wrote: Regarding freezing the values that are used..... Base a pivot table on the list of random values. Since the pivot table's cache is a snapshot of the source data, the pivot table will continue to work with those values until you referesh the PT data. Of course, the random value formulas will keep generating new numbers, but at least you'll be able work with a static list. For random numbers without duplicates, try this website: http://www.exceltip.com/st/Selecting...s%09/1110.html Does that help? *********** Regards, Ron XL2002, WinXP "Angyl" wrote: First, my experience: Some in VB programming in WORD and very basic knowledge of Excel otherwise. Now the problem: We want to run random drug tests on employees for a client of ours. I've created a spreadsheet that uses the =INDEX(A2:A16,INT(RAND()*15)+1) Function I found here but there are two things I need to know if I can adjust with that. 1. Theoretically this could end up being a legal document when we pull names out of it and the employee contests the selection process. So we need to be able to "save" the results. Problem is, if you save this document, opening it automatically refreshes this formula and causes it to pick a new name. Any way to stop that? 2. Out of say, 30 names in a list, we would sometimes want to select 10 or 8 or 2 of them at a time for testing, so we don't want duplicates -- else I'd just put the formula in multiple cells. Is there any way to adjust the formula so that it does not duplicate itself? Or perhaps...better yet, have it post the results from each calculation into static cells elsewhere? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Random Number response | Excel Worksheet Functions | |||
Random Sampling | Excel Discussion (Misc queries) | |||
Generate random numberes using reference to the other cell. | Excel Worksheet Functions | |||
VBA "Rnd" Function: Truly Random? | Excel Discussion (Misc queries) |