in this video we're going to generate random numbers in excel including unique random numbers
in excel by the end of the video we're going to get to the point where we're going to create
a function called rand unique and we're going to type a lower boundary for this let's say
starting from 4 and upper boundary let's say 12 and then we're going to say how many
numbers we want to generate so if i do 5 this will generate five numbers between four and
twelve and there it is random five numbers just like that so if we go here and type something else
that should regenerate and give us another five random numbers between our low number of 4 and
high number of 12 and this is how it's going to work now that function does not exist in excel
rand unique but i've made that function under name manager up here and that's what we're gonna do
in this video so first let's just figure out how we're gonna create a random number
in excel right so if i just wanted to generate a random number i could have
just done rent or rent between that creates a single number but if you want multiple
numbers generated then you use rent array it creates an array of random numbers
so if i go ahead and do this and do a row number so for row number that basically in our instance
would mean how many numbers we want generated so if i want 10 random numbers i would do 10
comma then we have the second argument which gives us how many columns of numbers we want to generate
in this case i'm going to do one column of numbers comma and then we have the minimum and maximum
boundaries for our random numbers so if i do something like 1 to begin with and
40 as the highest boundary so a number between 1 and 40 if i close parenthesis right now and hit
enter you will see we can generate a random number between 1 and 40. now this doesn't guarantee we're
going to get a unique number it's just random 10 numbers in this case so if i update this to
say 5 we're going to get random five numbers again there's no guarantee it's gonna be unique and then
there's also this last argument here after comma to make sure that the number generated is actually
an integer rather than a decimal point number so we can do true as the last argument if i hit enter
see we get five numbers and as you can see in this particular instance i got two 14s so they're not
unique but we got these numbers so that's our rent array and if you don't care about your numbers
being unique this should be good enough you could just generate your list of numbers like this and
you're done now we want unique lists of numbers so for that reason i'm going to start by using
another function here sequence and sequence just generates a list of numbers like 1 2 3 4 5 6 7.
so imagine if i wanted to generate numbers between 1 and 200 i would type 200 as number of rows if i
close parenthesis and hit enter this will give us starting from 1 and then if i just keep scrolling
down let's do control down really quickly up to 200 so we generated a list of numbers
from 1 to 200. let's just make this a little smaller let's do 40. so now we're going from 1
through 40 like this what i'm going to do i'm going to go next to this and generate
another random list of numbers so i'm going to do this rand array function i did before and i'm
going to use the same number of lines i have here so i got c40 here so i'm gonna do 40 for this too
and then i'm going to basically close parentheses and hit enter and what this is going to do it's
going to generate 40 rows of random numbers for us here just like that so it's gonna match this first
sequence so at this point if i just sort these two columns by this randomly generated numbers columns
i'm gonna get a list so let me show you what i mean so if i just select this list here comma
and then say sort by column number two column index being two if i hit enter that's going to
let me zoom out a little bit so that's going to basically just sort this by this column
up here and what that means it's going to shuffle these numbers between 1 and 40 in some random
order so we got c like these numbers so this point if we wanted like five numbers we could just grab
this first five and it should give us five numbers between one and 40. so how do we now combine
all of this different steps to one step so what we could do we could just go back here
see now here i have two separate functions for a column and b column i'm gonna go ahead and grab
this red array function and cut it out of here without equal sign and i'm gonna go to this other
first function here i'm gonna use h stack function which is going to horizontally stack our two
arrays next to each other so what that means i'm going to go after this first function do a comma
and paste that second function i used to have that used to generate 40 random numbers like this close
parenthesis hit enter and now i got one function that generates this range or array of two columns
which means now i can just take this whole thing and put that inside of a sort function
like so and then go after this comma and say sort this thing by the second column like that close
parenthesis hit enter now it's going to be this i can't get rid of all of that and now we got
one function here that gives us all of this it gives us all of these numbers generated
and then sorted by the second column and every time we update this order is going to shift
okay so fair enough so now we need to grab x number of values here let's say we want the first
5 or something like that right so to do so we're going to go here and use index function so i'm
going to do index close parentheses put this whole array inside of it go after this do a comma and
then i need to grab the first five values and the way you could do that is by having an array like
this like one two three four five like five values like this if i do this i close parenthesis and hit
enter you'll see it grabs me the first five values in there but i don't want this to be hard-coded
because if this was like 15 numbers then i have to do this semicolon and keep doing this
so instead of doing this we can just use another sequence function here so we can just say sequence
if i just learn how to type that correctly without hitting enter there it is we're gonna
then type that number five so if we type five it's gonna generate between one and five that array
or if i type 10 it's going to go 1 to 10. so this way we just have this and it gives us 5 numbers
between in this case 1 and this upper limit being 40. now if i change this 40 to say 60
then it's gonna give us five numbers and if we do 60 here we also have to do 60 here by the way
to match them so if both of these are 60 now it's gonna generate five random numbers between one and
sixty if i'd enter that's exactly what we're gonna get if we want more than five we can update this
to let's say eight numbers between now one and sixty and that is going to generate that list
so this is pretty much our core functionality that we need except that right now our boundary starts
from 1 and it goes up to 60 so we don't set a minimum and upper boundary we just basically have
upper boundary only and then we also have this upper limit and that's it so how could we make
this in a way that we can have a lower boundary and upper boundary so for that we're going to use
a couple of things we're going to convert this to a lambda function first of all so by using
a lambda function we're going to be able to later then create a named function so it's easier to use
this function and then it's also going to allow us to basically pass some arguments and use those
arguments in this function so first of all let's just go here and after equal sign basically put
this whole thing i'm going to just push this right a little bit i'm going to put that whole thing
inside of a lambda function and for this lambda function i want three parameters i want the low
boundary i want the high boundary the highest number the lowest number and then i want how
many numbers i want generated so we can just call this limit or count or whatever you want to call
it so i guess i'm going to call it limit comma and those are three parameters i want to pass
to this function the way i'm going to use those parameters first of all i'm gonna then have this
index function that i had as the calculation so i'm gonna go after this whole thing and close
parentheses so this is now gonna be my lambda function and in this lambda function we would
pass those parameters so if i just wanted to do it right here so we can test it i can open another
set of parentheses here and just pass my numbers so let's say i want this to be between 1 and 60
and then i want 8 numbers like this so this is the way we're going to run this function and to be
able to modify these numbers we need to pass those here within this calculation with it so instead of
the 60 i did here i'm going to use this high which is the 60 parameter so i'm going to type high
here and i'm going to type high on this one and then instead of this 8 right here i'm going to
use that third parameter up here which is the same as the third parameter here which is limit
like that and notice that right now i did not use that low parameter at all so far so
i'm going to keep it at this let's just see what happens before we continue i'm going to hit enter
and you can see now i got my numbers so i still get my generated numbers between 1 and 60
and 8 of those numbers now i can go back here and update this 8 right here to say 5 hit enter
and that's going to generate this and then i can go back and change this upper limit say to 20 now
we're going to get 5 numbers between 1 and 20. so if i enter see we got 5 unique numbers between 1
and 20 so that works the only thing left for us to do is to incorporate this lower boundary in this
whole thing too so we can also choose what's the lowest number we can do because right now this one
doesn't really do anything because we didn't use this low anywhere here in our calculation
how exactly are we going to do that if we wanted to generate let's say numbers between 4 and 14
let's say this is our lower boundary and this is our upper boundary we need to figure out how many
numbers would that be between 14 and 4. let's actually make this easier let's do between 14
and four and six not fourteen four and six so if my lower boundary was four upper boundary was six
the numbers i would have would be four five and six so there are three possibilities between those
numbers that is the same as doing let's take this upper boundary the high minus the low
now high minus the low give us two but we want three possibilities so we can add one to this and
that will be number of possible numbers we want so we want three possible numbers here so it's going
to be again high minus low plus one and it's gonna give us these three numbers now if i use those
three numbers in my sequence to generate three numbers between one and three that will give me
one two three the problem is i want my numbers to start from four and go to six instead of starting
from one and three and i can do that by going here to the sequence function after i generate from one
to three and i can add to this this lower boundary right here so if i add the lower boundary the four
to it we get five six seven which is one more than what i actually need here so i need four five six
but i got five six seven so at this point i can just do minus one and that would generate
our numbers between these low and high boundaries like this so that's the logic we're going to use
in this function right here so first of all let's just do this part so what i said i said we need to
do between upper boundary lower boundary plus one that's how many numbers we need to generate right
so i'm gonna go over here and right here instead of just doing the high which is what i got at
the moment i'm gonna do high minus low plus one and that same exact thing we're gonna do for the
second array because we need two arrays of the same size so i'm gonna do high minus low plus
one and that should take care of that part so it should now figure out how many numbers we need
generated in each one of those sequences and that should give us this except again the problem here
this is not going to have this part where we add the lower boundary minus 1 to make sure that our
numbers start in the right place because now our numbers start from one and for that reason i'm
gonna go after this whole index function right here so if i look here see this red parenthesis
we have red parenthesis here which is closing that index function so i'm going to go after
that whole thing and add my low boundary minus 1 to do that offset so if i'd enter right now
let's actually zoom out a little bit and get rid of all of this stuff so at this point now i
should have my function so if you look here we're generating a number between 1 and 20 and we get
5 of them so if i update this to generate a number between 11 and 20 and get five of these that's
what we should get see i got 11 as the lowest number let's just type something in different
cells so we get some updates so see now we get our five numbers and they are going to be between 11
and 20 and it generates those numbers for us now if you want different boundaries let's
say we want between 5 and 25 and we want 10 unique numbers between 5 and 25 we can do this
and that's going to give us the 10 numbers and again if i just type so it regenerates
that list that will give us our numbers again between this 5 and 25 and 10 of those
so now all we have to do is just take this lambda function and store it as a named function
so we can easily use this without having this crazy formula in the cell so i'm just gonna
basically not have this calling part just have this part of this function i'm gonna copy that
with equal sign hit escape go to my formulas and go to my name manager
i already made this but i'm going to make a new one so you can see how this works so
i'm going to go under new here we're going to name this function so i call it rand unique
it's just a function name and here we're just gonna paste that lambda function ctrl v with
equal sign and everything and if i press ok now we should have this rand unique function
stored in our spreadsheet which means i should be able to just go here and do equals rand unique
and say what's the lower limit let's say 7 and the upper limit let's say 21 and we want 7 numbers
numbers between 7 and 21 and we got 7 numbers here so here we go we got seven numbers
between 7 and 21. if i want 9 of those i can do that we got 9 numbers between 7 and 21
and now we got our function that could be used to generate a unique list of random numbers and that
should do it for this video thanks for watching please subscribe and i'll see you the next one