Up next in 10
Using IF and IFS Functions in Excel
1K views
Feb 7, 2025
Learn how to use Excel's IF and IFS functions to automate decision-making in your spreadsheets. This video covers basic syntax, practical examples, and tips for handling multiple conditions, helping you categorize data and streamline your workflow efficiently. Topics Covered: 00:00 Understanding TRUE and FALSE in Excel 00:50 Using the IF function for calculations and messages 03:40 Creating nested IF formulas for multiple conditions 05:20 Simplifying logic with the IFS function
View Video Transcript
0:00
welcome to this tutorial on the if and
0:02
ifs functions in Excel let's start by
0:04
understanding something fundamental true
0:06
and false Excel uses true and false as
0:09
logical values to represent the outcome
0:11
of a condition or a comparison for
0:14
example if you compare two numbers like
0:16
this formula Excel will return true if
0:18
the value in A1 is greater than the
0:20
value in A2 or false if it is not in
0:23
Excel true is equivalent to the number
0:26
one and false is equivalent to the
0:28
number zero when used in calculations
0:30
for instance if you write true + one
0:33
Excel will give you the result two
0:35
similarly false Plus One will return one
0:37
the if and if functions use logical
0:39
tests that result in true or false to
0:41
decide what output to return essentially
0:44
you're asking Excel to evaluate a
0:46
condition and take action based on the
0:48
result let's start with the IF function
0:50
this function lets you create a simple
0:52
decision-making process in the formula
0:54
shown on the screen if the value in A1
0:56
is greater than A2 Excel will return yes
1:00
if not it will return no let's look at a
1:03
practical example imagine you want to
1:05
evaluate sales performance and
1:06
categorize it as either good or needs
1:09
Improvement based on a target of selling
1:11
at least five items here's the formula
1:13
you would use this formula checks if the
1:15
value in cell C2 is greater than or
1:18
equal to five if it is the result will
1:20
be good otherwise it will return needs
1:23
Improvement for example if the value in
1:26
C2 is four the formula will return needs
1:29
Improvement now to apply this formula to
1:31
all rows simply double click the fill
1:34
handle and Excel will copy the formula
1:36
down the column once applied you'll
1:38
notice that products with five or more
1:40
units sold are labeled as good while
1:42
those with less than five units sold are
1:45
categorized as needs Improvement instead
1:47
of hardcoding the text good and needs
1:50
Improvement directly into the IF
1:51
function you can store these values in
1:53
cells and reference them in your formula
1:56
this approach makes your formulas more
1:57
flexible and easier to update for
2:00
example let's write good in cell G2 and
2:03
needs Improvement in cell G3 then we
2:06
update our formula to reference these
2:07
cells why is this useful if you ever
2:10
need to change the labels for instance
2:12
replacing needs improvement with below
2:14
Target you can simply edit the text in
2:16
G3 and the formula will automatically
2:19
update across all rows this eliminates
2:21
the need to manually edit the formula
2:23
itself saving you time and reducing the
2:26
chance of Errors by using cell
2:28
references you're making formula is more
2:30
Dynamic and adaptable to changes thef
2:33
function isn't just for displaying
2:34
messages based on Cell values it can
2:37
also perform calculations directly
2:39
within the formula let's see how this
2:41
works with a practical example suppose
2:43
you want to apply a 2% discount to
2:45
products priced at 500 or above for
2:48
products priced below 500 no discount
2:50
will be applied to achieve this we use
2:53
the formula shown on the screen here's
2:55
how it works the Formula First checks if
2:57
the value in B2 the product price is
3:00
greater than or equal to 500 if the
3:03
condition is true it calculates 2% of
3:05
the price in B2 by multiplying it with
3:08
2% if the condition is false the price
3:10
is less than 500 the formula returns
3:13
zero indicating no discount for example
3:16
if B2 equal 800 the formula will
3:19
calculate 800 * 2% which equals 16 if B2
3:23
equals 450 the formula will return zero
3:27
because the price doesn't meet the
3:28
condition this demonstrates the
3:30
versatility of the IF function it not
3:32
only allows you to categorize data but
3:34
also to perform calculations dynamically
3:37
based on specific conditions next let's
3:39
talk about nested if functions a
3:42
powerful way to handle multiple
3:43
conditions in Excel but first what
3:45
exactly is a nested if a nested if is
3:48
when you use multiple if functions
3:50
inside one another to evaluate more than
3:52
one condition it allows you to create
3:54
complex decision-making logic within a
3:56
single formula let's build on the
3:58
example from earlier where we applied a
4:00
2% discount to items priced at 500 or
4:03
above what if you want to offer
4:04
different discount rates based on the
4:06
product price for instance a 2% discount
4:09
for prices of 500 or above a 3% discount
4:12
for prices of 1,000 or above a 5%
4:15
discount for prices of 1,500 or above
4:19
and a 7% discount for prices of 2,000 or
4:22
above you can achieve this using a
4:24
nested if formula shown on the screen
4:26
here's how the formula works it starts
4:28
by checking if the price price in B2 is
4:31
2,000 or more if true it calculates 7%
4:34
of the price if not it moves to the next
4:36
condition is the price, 1500 or more if
4:39
true it calculates 5% of the price if
4:42
that condition isn't met it checks if
4:44
the price is 1,000 or more and applies
4:46
3% and so on finally if none of these
4:50
conditions are true the price is below
4:52
500 the formula return zero once you've
4:55
written the formula double click the
4:56
fill handle to copy it down the column
4:58
and apply it to the ENT ire data set now
5:01
each product will have the correct
5:03
discount calculated based on its price
5:05
this is how nested if functions enable
5:08
you to handle multiple conditions
5:10
efficiently however as your logic
5:12
becomes more complex it's worth
5:13
considering Alternatives like the ifs
5:15
function which can simplify your
5:17
formulas and make them easier to read so
5:19
next let's talk about the ifs function
5:21
the ifs function is designed
5:22
specifically for testing multiple
5:24
conditions without the need to Nest
5:25
multiple of functions it provides a
5:27
cleaner and more straightforward way to
5:29
handle complex logic here's how the ifs
5:32
function works it evaluates conditions
5:34
in the order they are written and as
5:36
soon as it finds a condition that is
5:37
true it Returns the corresponding result
5:40
you don't need to add else logic like in
5:42
the nested IF function because the ifs
5:44
function stops evaluating once a
5:46
condition is met for example let's
5:48
recreate the same discount scenario
5:50
using the IF function the first
5:52
condition checks if the price in B2 is
5:54
2,000 or more if true it calculates 7%
5:57
of the price if not it checks if the
6:00
price is 1,500 or more and so on
6:03
following the same logic as our nested
6:05
if formula as you can see the ifs
6:07
function eliminates the need for
6:09
multiple parentheses making the formula
6:11
shorter and easier to understand one
6:14
important thing to note about the ifs
6:15
function is that it doesn't return a
6:17
value if none of the conditions are true
6:19
instead it will result in an hashtag in/
6:22
aerror to prevent this you can add an
6:24
additional condition which checks if the
6:26
price is less than 500 and then return
6:29
zero this works because the ifs function
6:31
doesn't have a built-in argument for
6:33
handling a false condition alternatively
6:35
you can include a true condition at the
6:37
end of your formula as shown on the
6:39
screen to act as a fallback or default
6:41
value the true condition ensures that if
6:43
none of the previous conditions are met
6:45
the formula will return a specified
6:47
result instead of an error this
6:49
flexibility allows you to handle all
6:51
possible scenarios and make your
6:53
formulas error free thank you for
6:55
watching I hope you found this tutorial
6:56
on the if and ifs functions helpful if
6:59
you did please share with others don't
7:01
forget to like subscribe and hit the
7:03
notification Bell so you never miss an
7:05
update on our latest Excel tips and
7:07
tutorials your support helps us create
7:09
more valuable content for you thanks
7:11
again and I'll see you in the next video