If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
GENERATE RANDOM NUMBERS BUT EXCLUDE A NUMBER IN THE SEQUENCE
I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE WHATEVER
IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED TO GENERATE 1,2,3,4,5,6,7,OR 9. IM STUMPED? |
#2
|
|||
|
|||
I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE
WHATEVER IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED TO GENERATE 1,2,3,4,5,6,7,OR 9. IM STUMPED? On way: In A2:A100, put =RANDBETWEEN(1,8) In B1, put =RANDBETWEEN(1,9) In B2, put =A2+(A2=B1) and copy down to B100 Use the values in column B. |
#3
|
|||
|
|||
I might of explained it wrong. I want to use random(1,9) but for example i
dont want 5 in the random possibility.Is that possible? "Jay" wrote: I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE WHATEVER IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED TO GENERATE 1,2,3,4,5,6,7,OR 9. IM STUMPED? On way: In A2:A100, put =RANDBETWEEN(1,8) In B1, put =RANDBETWEEN(1,9) In B2, put =A2+(A2=B1) and copy down to B100 Use the values in column B. |
#4
|
|||
|
|||
Did you try "Jay"s solution? If you copy A2:B2 down as far as desired,
you get results (in column B) for each cell in the range [1...9] excluding the value in the "cell above it". This post, at least as far as I can tell, doesn't add any information that would indicate that "Jay"s solution doesn't fit. Do you *always* want to exclude the same number? Do you want to exclude more than one number (e.g., 8 and 5)? In article , "Tracker" wrote: I might of explained it wrong. I want to use random(1,9) but for example i dont want 5 in the random possibility.Is that possible? |
#5
|
|||
|
|||
Yes that helps.but lets say i needed to randomize 1-9 in nine cells that
exclude the number in the top cell of the nine cells? "JE McGimpsey" wrote: Did you try "Jay"s solution? If you copy A2:B2 down as far as desired, you get results (in column B) for each cell in the range [1...9] excluding the value in the "cell above it". This post, at least as far as I can tell, doesn't add any information that would indicate that "Jay"s solution doesn't fit. Do you *always* want to exclude the same number? Do you want to exclude more than one number (e.g., 8 and 5)? In article , "Tracker" wrote: I might of explained it wrong. I want to use random(1,9) but for example i dont want 5 in the random possibility.Is that possible? |
#6
|
|||
|
|||
Tracker,
Another way, which might be a little more complicated is to use IFs for different ranges. For example, if you wanted a random number between 1-4 or 6-9 (ie, not 5) you could make a formula that 50% of the time gives a number between 1 and 4 and 50% of the time gives a number between 6 and 9. This one's easy though, because it's 50-50. If you want to remove numbers other than 5, you'd have to modify the proportions. =if( rand() 0.5, RANDBETWEEN(1,4), RANDBETWEEN(6,9) ) Let's see. If you're always working between 1 and 9, and the number you want to exlude is "X", then: =if( rand() (X-1)/8, RANDBETWEEN(1,X-1), RANDBETWEEN(X+1,9) ) I have to run, so I haven't checked if this formula works, but it or something like it should do the job. Eric "Tracker" wrote: Yes that helps.but lets say i needed to randomize 1-9 in nine cells that exclude the number in the top cell of the nine cells? "JE McGimpsey" wrote: Did you try "Jay"s solution? If you copy A2:B2 down as far as desired, you get results (in column B) for each cell in the range [1...9] excluding the value in the "cell above it". This post, at least as far as I can tell, doesn't add any information that would indicate that "Jay"s solution doesn't fit. Do you *always* want to exclude the same number? Do you want to exclude more than one number (e.g., 8 and 5)? In article , "Tracker" wrote: I might of explained it wrong. I want to use random(1,9) but for example i dont want 5 in the random possibility.Is that possible? |
#7
|
|||
|
|||
Sounds like that will work.Thankyou
"Tracker" wrote: I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE WHATEVER IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED TO GENERATE 1,2,3,4,5,6,7,OR 9. IM STUMPED? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combo Box & Text Box | AccessRookie | Using Forms | 3 | April 6th, 2005 11:33 PM |
Create start and end number for 'blocks' of contiguous numbers | Deb | General Discussion | 7 | March 24th, 2005 07:21 PM |
Field in Table | LMB | New Users | 14 | October 11th, 2004 07:13 AM |
Random generated numbers | Denise Burnett | Worksheet Functions | 2 | October 8th, 2003 01:25 AM |
Random Numbers | L. Howard Kittle | Worksheet Functions | 1 | September 18th, 2003 07:20 PM |