GENERATE RANDOM NUMBERS BUT EXCLUDE A NUMBER IN THE SEQUENCE

T

Tracker

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?
 
J

Jay

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?
Click to expand...

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.
 
T

Tracker

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?
 
J

JE McGimpsey

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)?
 
T

Tracker

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 said:
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)?


Tracker said:
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?
Click to expand...
Click to expand...
 
E

Eric

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 said:
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 said:
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)?


Tracker said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Top