How to use the IFS function in Excel
How to use the IFS function in Excel
Learn how to use the IFS function in Microsoft Excel. This tutorial demonstrates how to use Excel IFS with an easy to follow example. This is a new Excel function that is available in Office 365 and Excel 2019 and replaces the old method of nesting multiple IF functions in a formula. You can enter up to 127 conditions. We also show you a workaround that allows you to enter an ELSE condition in the IFS function.
IFS Function (and sample data):
https://www.techonthenet.com/excel/fo…
Excel Functions/Formulas:
https://www.techonthenet.com/excel/fo…
Excel Tutorials:
https://www.techonthenet.com/excel/in…
Don’t forget to subscribe to our channel for more great Excel tutorials!
Visit us at: https://www.techonthenet.com
or follow us on:
Facebook: https://www.facebook.com/techonthenetcom
Twitter: https://twitter.com/tech_on_the_net
Pinterest: https://www.pinterest.com/techonthene…
Content
0.02 -> The IFS function is a new Excel function
that is available in Office 365 and Excel 2019
7.72 -> The IFS function lets you specify
multiple IF conditions within one function call
13.08 -> It replaces the old method
of nesting multiple IF functions
17.3 -> and lets you enter up to 127 conditions so
your formula should be easier to read and understand
23.76 -> The only downside to this function
is that you can't specify an ELSE condition
28.68 -> but not to worry we have a workaround
that we'll show you later in this video
33.18 -> So let's get started by looking
at an example of how to use the IFS function
38.38 -> Here we have a table of
data that consists of a list of items
42.34 -> Let's use the IFS function to return a
different value depending on the item
48.02 -> Let's begin by entering the IFS command
51.2 -> As you can see the IFS function takes
condition/return value pairs
55.58 -> This requires you to enter one parameter
for the condition to test for
59.86 -> and a second parameter
for the value to return when the condition is TRUE
64.42 -> You can enter up to
to 127 of these pairs
68.44 -> So let's enter our first
condition/return value pair
72.24 -> We'll enter A2="Apple"
as the condition
75.96 -> and "Fruit" as the return value
79.54 -> What this means is that if
the value in cell A2 is equal to "Apple"
84.18 -> the IFS function will return
the value "Fruit"
87.7 -> For our next pair, we will enter A2="Potato"
as the condition
92.214 -> and "Veg" as the return value
94.36 -> In the second pair, when the value in cell
A2 is equal to "Potato"
98.62 -> the IFS function will return "Veg"
102.32 -> Now for our third pair,
we will enter A2="Steak" as the condition
107.22 -> and "Meat" as the return value
110.1 -> Let's complete the command to see
what results we get
113.8 -> You should see the value
"Fruit" appear in cell C2
118 -> Since cell A2 is equal to "Apple",
the IFS function will return a value of "Fruit"
123.6 -> Now let's copy our formula into
cell C3 to C5
128.02 -> Now we have the return value
of "Veg" in cell C3
131.86 -> and a return value of "Meat"
in cell C4
135.6 -> Notice that in cell C5,
we have a return value of #N/A
140.2 -> This value was returned because
none of the condition evaluated to TRUE
144.4 -> in our IFS formula
145.84 -> Normally we would include
an ELSE condition to handle this case
149.58 -> and prevent the formula from
returning the #N/A error
153.4 -> But since the IFS function
doesn't have an ELSE parameter
156.44 -> we've come up with an easy workaround
159.4 -> So let's rewrite our formula
in column D to include our ELSE condition
164.06 -> The formula will begin the
same as the original formula
167.62 -> with our three
condition/return value pairs as follows:
172.58 -> Now to avoid getting
the #N/A error
174.74 -> we'll create one final condition/return
value pair at the end of the formula
180.51 -> We'll enter TRUE as the condition and
"Misc' as the return value
186.84 -> Since the boolean value TRUE
is always TRUE
189.9 -> Once the IFS function reaches
this condition
192.82 -> it will evaluate to TRUE and
return the "Misc" value
197.48 -> Now let's copy our formula down
into cell D3 to D5
201.92 -> This time we can see that instead of
returning the #N/A for "Coffee"
206.16 -> The IFS function has
returned the value "Misc" in cell D5
210.3 -> Our ELSE condition
has worked
213.91 -> This completes our example of how
to use Excel's IFS function
218.96 -> If you would like to
see more examples
221.06 -> or would like to download the
sample data we used for this tutorial
224.12 -> please visit our website at
TechOnTheNet.com
227.7 -> If you found our tutorial helpful, please
leave a like on this video
232.41 -> And don't forget to subscribe to our YouTube
channel for more great Excel tutorials
Source: https://www.youtube.com/watch?v=pdJ_OiTqzT4