Need up to 30 seconds to load.
hello and welcome to this video tutorial
from computer gargle comm and in this
video we are going to look at how to
randomly assign names to different
groups using an excel formula so we're
going to be very quickly and easily
distributed these names randomly across
three different groups that is the plan
here we have 12 names we want them in
three different groups and let's imagine
the names of the three groups are red
blue and green so these are my free
waves or whatever you want to call them
now let's look at two different ways of
doing it here's why number one now
they're both going to use the excellent
choose function in excel so in cell b2
let's imagine I want to put the group
name here I shall start with the choose
function let me zoom in a little bit
more so we can see the formula to our
right and for anyone who may not be
familiar with or I've seen any of my
previous videos on this and you want to
check this one out
it's very good flies under the radar a
little bit it's one of those kind of
logical functions and it will choose a
value based on a list of values really
so what we're going to get is the Rand
between the function involved to
randomly produce a number between number
one and number three so I've just
separated those with a comma there three
different groups yet close off the
bracket put in the comma the choose
function now has its index number which
is a randomly produced number between 1
and 3 it now wants the three values we
could write these in any order just
going to put red in then blue in and
then green
and I'll close my bracket press ENTER
and copy that down to the bottom and
there's the formula randomly produce a
number between 1 and 3 and assign the
values red blue or green to one of those
index numbers and there's a quick way
that we can quickly randomly assign
those 12 names into three different
groups now one thing that technique
doesn't do is distribute them evenly I
can see straight away this is actually
quite a paired example is not there's
not often this bad that but out of 12
I've managed to get is that 8 at 7 sorry
greens at 7 greens just 3 blues and 2
Reds so you know there's a lot more in
the green group than than any others and
maybe depending why you're doing it
that's okay but if you're looking for
some even distribution then it's not
enough and we're going to look at
another approach so let's look at how we
can evenly distribute those twelve names
into three different groups and we will
be using the choose function again but
this time we will also be using the rand
function instead of randbetween and i'm
going to enter this in column c you
could enter this anywhere you wish I'm
just going to put it in the next empty
cell along it's a very simple function
it's just going to have two brackets on
the end no arguments so Rand short for
random and just like v-- today function
with the brackets there and this will
produce a random number between zero and
one which by itself does not look maybe
too useful but it is a random number and
we can then kind of round that or rank
it and whatever you may want to do with
that number but it is randomly
distributed and in column B we are now
going to do the formula similar to
before to distribute them into groups
so the choose function let me zoom again
for ease of reading choose function
index number assign a different value to
it the index number this time I'm going
to rank those values now some of you
watching this video there's a good
chance you've heard we talked about
ranking values of them quite a bit on it
I have a sports league table course
where I show how to rank players and
teams in two league positions based on
varying conditions and here we just want
to rank those numbers so it's cell c2
the first value in that range and then
we have sales C 13 - C - if I go
backwards because that formula it's kind
of overlapping that cell and I'll make
that absolute and we're then going to
divide that value by four so I have 12
names I want them distributed evenly
into three different groups and if I
touch through 12 names into three groups
there will be four in each group so I
want to divide that before
bear in mind please that you could have
that value in a different sale it may be
like how many people in each group you
just have four and sell f2 then a
reference f2 and then people can change
that easily in the future if that's what
you need from this into the one-off
scenario I'm just going to type for now
what I also want to do is round these
numbers because we are ranking with lots
of decimals here we could produce some
errors when dividing in or with this
kind of stuff so I want to bring in the
round up function to always round a
value up and I'll get around it to know
digits you just round it up to an actual
index number which is what the choose
function wants so round up the resulting
value to a full on whole index number
and then we're in the choose function
and putting in our values just like
before so as I rattling my red blue and
green here and I love it when the
formula
to go into a different line shows you're
doing something exciting herb it out can
just about squeeze this one in so red
blue and green based on those values if
I press ENTER Annette and copy that
formula down here we have some evenly
distributed names I have four names in
green where's my red one for in red and
four in blue we have an equal
distribution by using the rand function
and then of that one so that is a an
approach that we can make purely using
Excel formulas now we have used the
rounder between in the first example and
the rand function in this example and as
you can see right now as I go around
doing things they're constantly
evaluating as working stuff so it might
be depend on your scenario for using
this that once you have written in those
formulas and distributed those values
you might want to select the cells in
question like these these rained these
render ones really and quickly convert
those into values and you could do that
by highlighting those sales hovering
your mouse over the edge border of that
range and with your right mouse button
click and drag away from the range and
then back onto the range and release
your mouse and copy them as values only
that is a real quick way that you can
replace them with values so it doesn't
continue to calculate each time any
Marten to do that that range as well if
you're going to do that otherwise
another option would be to go to the
formulas tab and turn your calculation
options on to manual instead of
automatic then you can always calculate
them with the calculate sheet button
when you need it
yet another option will begin macros to
do that kind of stuff so depending this
is something you're doing very very
often I'll where it's just a kind of one
or thing and he just wanna bash in a
formula get it done convert to values
now
have been assigned very quick and simple
into the point you can even copy a
formula we've done before and do that it
could be as simple as that for you I
hope you found this video useful please
check out some of our other video
tutorials on our YouTube channel and
come and check us out at computer gargle
comm
you
