in this video let's create a formula that will select a random item from a list and the same
formula will work in excel and google sheets i'm not going to be using any new functions in
this video so whatever version of excel you have should still work so in this particular situation
i have this list of names and i would like to randomly select one of those so to do so
first we're to start with rand between function so this allows us to generate a random number between
two numbers so as an example if i type 1 as a low and as a high i'm going to do 10 this should
generate a random number for us between 1 and 10. so if i enter see it gives me one apparently
if i just make some changes to my spreadsheet that should automatically update and that number
should change now sometimes it's going to be the same number but at some point see it's going to
change to a different number but that number will be a random number between 1 and whatever number i
let's go ahead and erase this so the reason i chose 10 here is because i have 10 names in this
list now it would be nice to automatically get that 10 to just match the list size
instead of hard coding the 10 here so we can do so by using something like
counta function and select this range of names just like that so instead of hard coding 10
now i do the upper limit with this counter function that will basically figure out how
many names we got and generate a number between the first and the last one we have in the list
like so now if you have blanks in this range then you could use something like rows instead
because counter will not count any blanks so if you have blanks in your list then it's probably
best to do something like rows to make sure you actually get a 10. but in this case if you had
your list going this way then you would have to choose columns instead of rows now in my case
i'm not worried about blanks so i'm gonna do counter just know if you do have blanks just
adjust accordingly so now i just got this function that randomly generates a number from one to
however many items i got in my list so the next thing is going to be to pick the item
and i'm going to do that using index function so i'm going to do index and in index function the
first one is going to be the reference and that reference is going to be the same list that was
selected over here for counter function and i'm going to do a comma and the second part would be
the item position so if i do 1 that would be bob if i do 2 that would be anna but we want a random
one and that's why we wanted this random number here so i'm gonna leave this function over here go
after this close parenthesis and hit enter and now see it grabbed linda from that list now if i make
any changes to the spreadsheet so it auto updates that function it will pick up a different name see
and sometimes it may be the same person too now again let's confirm that this same exact thing
works in excel so i'm going to copy this and we'll paste that in excel spreadsheet
like so hit enter and that should work exactly the same way see we got shane out of this list and if
i type some things here that should automatically update the person now if you're in excel in excel
you can actually change your spreadsheets to do manual calculations instead of automatic and the
difference would be right now anytime i enter anything in any cell in excel this automatically
regenerates to a new name right so if i didn't want that to just happen like that what i could
do i could go under this formulas section on top and under formulas i can change my calculation
options to manual and now see we got amanda here since i have this under manual calculation i can
make changes and it's not gonna generate another name but it will generate one if i go on top here
and click calculate now then we'll recalculate the spreadsheet and this function will update so we
get another name and every time i click calculate now again that should generate another name for
us just like that this is not something you can do in google sheets google sheets unfortunately
doesn't have manual calculation option but in excel you could actually do this
and that's how we can select an item randomly from a list of items and that should do it for
this video thanks for watching please subscribe and i'll see in the next one