Generate a Random Number but Exclude Some Numbers
This is going to be an exclusive video...well basically it's going to show you how to create random numbers and exclude a number (or set of numbers) from that list. There are three examples that I'll show. One showing how to exclude odd or even numbers from a random number list. The second will show how to exclude a set of numbers from a larger list. And the last example will show how to randomly generate a number in two steps but with in the second instance, it can not be a repeat of the first randomly generated number. Check out the video for the random goodness.
P.S. One thing to note on the first example is that you could get a number that is outside the RANDBETWEEN range (1 to 100) in the ODD number example. In that case it can be wrapped in an IF function to mitigate it. The function could be like =IF(ODD(RANDBETWEEN(1,100))=101,ODD(RANDBETWEEN(1,100)),ODD(RANDBETWEEN(1,100)))
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to / doughexcel
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Пікірлер: 41
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. 🍺🥂
@DougHExcel
7 жыл бұрын
Hi rockguitarist8907, glad you liked it, thanks for commenting!
I would be interested to know how to have Excel generate a random 3 digit number, whilst excluding a data list of 3 digit numbers.
This is exactly what I needed. Thank you.
@DougHExcel
2 жыл бұрын
You're so welcome!
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?
@DougHExcel
6 жыл бұрын
these might give some insights...kzread.infosearch?view_as=subscriber&query=countif the 1st vid might apply with COUNTIF if those numbers have a delimiter and can be separated into other columns. If the numbers can be transposed into separate rows, then the other videos might help too.
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?
@DougHExcel
6 жыл бұрын
Maybe this video can give some insight -> kzread.info?o=U&video_id=N_BrUTce1EI
What is the purpose of the small function in expression to get random number excluding previous random number please?
What is the name of the program you use to record and how do you make the key shortcuts appear in the recording?
@DougHExcel
4 жыл бұрын
Camtasia, there's an affiliate link in the description. The keyboard shortcut icons are a feature in the editing portion of the software.
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?
@DougHExcel
6 жыл бұрын
See this video for some insight => kzread.info/dash/bejne/q4eCk7ihnMS2pLg.html
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?
@DougHExcel
3 жыл бұрын
maybe one of these other videos will give insight kzread.info/dash/bejne/jJOWlcuMdcXcpKQ.html
How can you create 5 random numbers , excluding some and not getting repeat numbers? Like lottery numbers?
@DougHExcel
3 жыл бұрын
maybe one of these kzread.info/dash/bejne/aGykvJKuorCsm7Q.html
How do you get f9 to recalculate?
@DougHExcel
6 жыл бұрын
Depends on your keyboard configuration. You might need to toggle the FN key to get the function keys active.
How do you sort it from lowest to highest and does not refresh when recalculated
@DougHExcel
2 ай бұрын
it's a volatile function meaning it'll do refresh unless copy/paste values. An alternative is to put this into Power Query. Though not exactly same thing, take some ideas from video at kzread.info/dash/bejne/iY16ydWxqsK4lNI.html
does this work on mac because my laptop is struggling lol it just keeps saying "NAME?" what do i do to fix that?
@DougHExcel
3 жыл бұрын
AFAIK being on MAC should not affect 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 ###?
@DougHExcel
6 жыл бұрын
Not sure...but Goal Seek might help. See vids for insight -> kzread.info/dash/bejne/Yn2hps-Sp9zZiNI.html or kzread.info/dash/bejne/nGl3t8Ghg6i9ptI.html
@AszRox
6 жыл бұрын
do you think using Solver will be better or Goal Seek?
@AszRox
6 жыл бұрын
i figured it out: stackoverflow.com/questions/21782329/generate-n-random-numbers-whose-sum-is-a-constant-k-excel, thank YOU Doug, much appreciated of your prompt replies!
@DougHExcel
6 жыл бұрын
ah yes Solver...i almost forgot about that one. Yea that might be a better tool :-) ---> kzread.info/dash/bejne/qaSWpqRuerHfZZM.html
@DougHExcel
6 жыл бұрын
Nice Thanks for adding to the thread, it'll help others!
I tried doing this on google sheets but when I use one it says no valid input data
@DougHExcel
3 жыл бұрын
Hi Daniel Tigas, thanks for the comment though I don't know about Google sheets
Randbetween(1,100) and 101 shows up... lol
@DougHExcel
7 жыл бұрын
Yep, one of the quirks of this one if that it will round up on the ODD function. A way to take care of this is to use enclose the RANDBETWEEN in an IF function like this =IF(ODD(RANDBETWEEN(1,100))=101,ODD(RANDBETWEEN(1,100)),ODD(RANDBETWEEN(1,100)))
eveything was great until the small function made it result in #num result