Generate a Random Number but Exclude Some Numbers

What is the purpose of the small function in expression to get random number excluding previous random number please?

This is exactly what I needed. Thank you.

Hi Doug . . . I battled with the solution using SMALL and could not get an intuitive way of doing it. I propose the following in a new blank sheet:

1) In Cell A10, enter RANDBETWEEN(1,7) to generate the item to be excluded.
2) In Col A, enter ITEM in A1 and autofill A2:A8 from 1 to 7
3) In Col B, enter BINARY in B1 and this formula in B2 = IF(A2=$A$10,0,1). Copy B2 down to B8. This will place 1's in all target items and 0 in the cell whose value = the selected item (in A10), ie, the one to be excluded.
4) In Col C, enter MULT in C1 and let C2 = B2*A2 and copy C2 down to C8. The selected item will now be calculated as 0. The rest are the values we need to select from.
5) In Col D, enter "2nd Item" in D1 and let D2 = LARGE($C$2:$C$8,RANDBETWEEN(1,6)).
This is our answer. I conditionally formatted it to show in red if D1 = A10. It never did.

We read the formula as such: Find the Largest number in C2:C8 (which will exclude the Zero we forced earlier). Which "largest"? This is decided by a RANDBETWEEN function that generates numbers from 1 to 6 . . . Since the number to excluded has a value of 0, it will never be the largest.

Hope this is a good solution . . . though a bit elaborate.

does this work on mac because my laptop is struggling lol it just keeps saying "NAME?" what do i do to fix that?

is it possible to make function that will be choosing random number from range 1-36 AND 82-146 ? Or I have to write all included numbers?

How can you create 5 random numbers , excluding some and not getting repeat numbers? Like lottery numbers?

I tried doing this on google sheets but when I use one it says no valid input data

What is the name of the program you use to record and how do you make the key shortcuts appear in the recording?

Do you have a tutorial on how to count duplicate numbers in the same row? For example 171, I would like to create a formula that will say "YES" indicating that there's a duplicate in that particular row?

none of this is working for me whatsoever. Excel is stating that it thinks its an equation but cannot compute? wtf?!?
EDIT: it won't even do that basic "=CHOOSE(RANDBETWEEN(1,7),1,2,4,6,7,9,10)"

For your last example, let's say I want to add a 3rd row with a function that chooses a random number between 1 & 7, but I don't want to repeat the 1st or 2nd randomly generated number. What formula would I use to achieve this?

Yes, this is helpful but how do we add two formulas...let's say exclude odd but make sure the six random number add up to the sum of ###?

I want to create a sample (of ten) using random numbers (in excel) while excluding the ones which have already been selected above. How Excel handles this task?

How do you get f9 to recalculate?

Randbetween(1,100) and 101 shows up... lol

Thank you Doug! Someone at work was asking me how to do the third example a couple months ago saying they always wanted to know how. Tomorrow I can share your video with him to show him how! Cheers to the MrExcel contributor as well. 🍺🥂