0:00
hi everyone welcome to tutor's point
0:03
today we are diving into Excel to
0:05
explore a powerful function that can
0:07
simplify your formulas the switch
0:09
function we will see how it can make
0:12
your life easier by replacing complex
0:14
nested if statements and guess what you
0:17
can follow along with the practice file
0:19
Linked In the description so let's get
0:21
started so here we have this example
0:24
data set of salesperson their
0:26
performances and based on your
0:28
performances we want to calculate their
0:30
bonus percentages and here I have this
0:33
thing this table which says for
0:35
excellent we have to give 5% bonus for
0:37
good it is 3% for average it is 2% for
0:41
poor it is 1% and for nil it is of
0:43
course 0% so first I'm going to use the
0:47
IF function here I'm going to write the
0:48
function here and then I will show you
0:50
how I will use the switch function to
0:52
make it very simpler so let's start with
0:54
the IF function so here I'm going to
0:56
quickly paste to the IF function and as
0:58
you can see we have if if uh we have
1:00
nested IF function here that says if
1:03
this B2 this performance that is
1:05
excellent here if it is excellent then
1:07
it is going to be 0.05 which means 5% if
1:10
it is good then it is going to be 0.03
1:13
if it is average 0.02 if it is for 0.01
1:17
and in case none of these values match
1:19
then it is going to return zero for the
1:21
nail one right and notice that this is
1:24
too big for a formula right and we have
1:26
multiple parentheses here this can be
1:28
really confusing for a beginner right so
1:30
if I hit enter it is going to work now
1:32
here it's 0.05 we can simply change it
1:35
to percentage by clicking here and then
1:37
selecting the percentage option now if I
1:40
drag the cursor down we get all of the
1:42
bonus values which is perfect but now
1:45
I'm going to use the switch function
1:47
here to show you that how easy it can
1:50
become using the switch
1:53
function so here I'm going to write my
1:55
switch function now it's very simple
1:58
I'll write equals switch and then
2:01
parenthesis and then we write the
2:03
expression in our case it is going to be
2:04
the performance whether it is excellent
2:06
good average po or nil so I'll select
2:08
this cell and after that I'm going to
2:10
write a comma then we are going to write
2:14
conditions so here the first condition
2:16
is if it is going to be excellent all
2:19
right so it is excellent when it is
2:21
excellent it is going to be excellent
2:23
when the value is 0.05 so basically for
2:26
excellent value the bonus should be 0.05
2:30
simple and then comma and then similarly
2:33
for the this good so when the good is
2:37
there then the value is going to be
2:39
0.03 great and then uh we have the
2:43
average for the average uh it is going
2:46
to be 0.02 which means 2% and then we
2:51
have the poor and for the poor the value
2:57
0.01% and then finally comma
3:00
at the end we are going to specify
3:02
another value zero now this is going to
3:03
be in case none of these values match so
3:06
this zero is going to be for this nil if
3:08
I close the parenthesis as you can see
3:10
this formula is much simpler with a
3:12
single uh parenthesis so it's very
3:14
simple first we write the expression
3:16
which is B2 then we say if it is
3:18
excellent than 0.05 so this is the first
3:21
one all right so this is the second one
3:23
for good it should be uh 0.03 and then
3:26
for average it should be 0.02 and then
3:29
for poor it should be 0.01 and if none
3:32
of these are there then it should
3:33
automatically be zero very simple and if
3:35
I hit enter it's going to give me 0.05
3:38
so I'll just change the format here to
3:40
percentage and as you can see it's the
3:42
same 5% and if I just track the cursor
3:45
down as you can see we have the exact
3:47
same values 5% 3% 2% 1% and so on but
3:52
the difference here as you can see this
3:54
if function can be confusing with a lot
3:55
of parenthesis and a lot of nested if
3:57
statements and at the same time this
4:00
switch function is very easy to
4:04
right so that will be enough for this
4:07
video now make sure to download this
4:09
Excel practice sheet from the link in
4:11
the description below also if you found
4:13
this video helpful please like subscribe
4:16
and share it with your friends and I'll
4:18
see you in the next video
