hi I'm Jamie and welcome to teachers Tech today I want to show you how the switch function can
outperform if statements in Microsoft [Music] Excel if you'd like to follow along with today's
lesson I have the practice sheet Down Below in the description where you can click on it and
download it and work through it as I show you I'm kind of curious how many of you have heard of the
switch function in Microsoft Excel before and how many have used it and what are the results
when you're using it let me know Down Below in the comments so what we're going to do first of
all is I'm going to show you the F statement that I've already created in this one and I'm going to
create the same type of functionality with the switch statement down below so if you uh take
a look at the formula bar up here you can see I have the uh if statements it's nested ifs in it it
goes through and finds if this number matches to whatever it's going to give back uh the word like
average here so three equals average in this case I copied it down good is for and so on so it works
well it copies down but now we're going to create the switch uh show you the switch function in here
and I just want to show you why it's a little bit more streamlined and a little bit more efficient
to use especially if you're adding to it so let's go ahead and start this let's go ahead and start
the switch function right here in D12 I'm just going to place my equal sign and start typing
switch and I have it right here so we can take a look that what it does evaluates an expression
against a list of values and Returns the result corresponding to the first matching value if
there's no match an optional default value is returned and I'll place that at the end so let's
go ahead and select that switch and I'm going to start with selecting the cell that I'm going to
be looking at and it's going to be this one right here so that's my reference place my comma in and
all I have to do now is give it the first uh what it's going to be looking for so the number one
comma and if it is number one what do I want it to be well it's just going to say four and then I
can just Place Another comma put my two comma and then Place what's the two going to be equal to so
this is going to be below average like so and we can continue on so let me go ahead and fill this
out and so notice when I got to the end not rated there's no number that that's uh compared
to there so it's just going to be if it's not 1 2 3 4 5 live that's what's going to have the
option now notice when I do this it just has the one bracket at the end one bracket at the very
beginning not like the nested if that had had to close every one of those opening brackets so
if I was going to make this longer I can just quickly add another comma in here and continue
on with my list let's go ahead and hit enter and copy this down and you're going to see it gives
me the same results but the advantage of this is going in into this and formatting it if I needed
to add more when I compare it to this you can see this is a little bit longer and there's just a few
more things you have to adjust to make changes to let me give you another example now before I move
over to the example two I just want to show you that the switch function did work if it wasn't
listed it took the alternative not rate it there now at the bottom move over to example two and
we're going to do a operation so multiplication using the switch function so I'm going to have
this right here so here's total sales and we have regions but the commission is different
for each region so I'm going to be multiplying this by a certain number based on it switching
to uh north south east west central or other now I'm going to place my uh switch function in here
so I'm going to go ahead and get started but the first thing I'm going to do is just select this
cell and start my multiplication so this is going to be be multiplied and now I can start
my switch here and I'm going to choose this and start filling it out so it's going to be
based on D2 because it's going to look up what region it is and then so what I put my comma in
and then it's going to be saying North make sure that we have everything spelled exactly
the same and North is going to be a certain commission so I'm going to say it's going to be
0.05 and then we can place our comma and then go ahead and type our next one like so so we're
going to keep filling this out just like we did before so I'm going to go quickly finish this
up so now I have it all finished up I closed it up I have north south east west with the different
commission rates for each of them notice that I don't have Central in there because it's going
to be the other one uh the only other option and I'm just going to put zero there so that's what
it should get give back so let's hit our reenter and copy this down and so Central shouldn't be
anything if I copy it down and you can see that it is at zero so at each of these as I copied it
down it went through it switched it to which one I needed so this one went over to the South took
the 6 multiplied it by the 15,000 and we got it there so again another efficient way to use this
switch function in Microsoft Excel let's move over to another example for the last one so in
this example I started with an if statement and then I'm going to put a nested switch inside of
it let me explain what I want to have happened here so we're going to look at this column right
here look at difficulty level and it needs to assign a number to medium easy heart different
numbers and then it needs to look at urgency and assign a number to high low medium and it needs
to add these two together to get a final number but at the same time if any of these are blank
I need it to just return a priority score of zero so I'm going to click in where my formula
is and take a look at it so you can see I started with an if here and then I had an
or statement and I Ed the function is blank so it took a look at C2 and said if this is blank
then it needs to uh Return To Zero I'll show you that that will work but it also needs it
to look at D2 and do the same thing return zero now this is where the switch comes in
so switch it's going to take a look at C2 and we're going to just before like before assign
those numbers to what it says so you can see easy is one medium is two and hard is three
I've added an operation I need to add the two so I'm adding another switch to do this column
right here and doing the same low zero medium one and so on so those two are going to get
added and I have my closed bracket ETS at the end of just the two brackets so when this is
all said and done I can copy this down and it works for each of them and like I said if one
of these are blank if I go ahead and delete it it will turn the priority score to a zero using
the function so this is where it's coming in at the beginning with the if or is blank and here's
the zero returns so I hope you like these different ways to use this switch function
in Microsoft optic cell uh it can be a lot more efficient than just using the if statement and
using nested ifs give it a try let me know what you think if this comes in handy for you thanks
for watching this time on teachers Tech I'll see you next time with more Tech tips and tutorials