Groups In Excel

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