Hi, this is Wayne again with a topic “Using the New IFS Function in Excel”.
In this tutorial, we’re going to take a look at the exciting new ifs function in Excel. This is a new feature, a new function that was brand new in Excel 2019, and that also includes office 365. So before we dive in to this new ifs function, let’s take a look at the regular if function that’s been in Excel for many many years here we have a spreadsheet with a list of student numbers student names and their test scores. Let’S say this: is a college class they’ve taken a test and these are their scores.
Well, I could use the if function to do some basic automatic grading. So, let’s look at how to do that with the traditional f function that we’ve had for quite some time step. One would be to click on a cell tap equals if left parenthesis and we get a pop-up with some help.
It says if logical test value, if true comma value with faults, so I need a logical test. Let’S say if D 2 is greater than 59, then and I’ll put a comma to represent. Then, if you look at the help text that we’re getting here, it says now value if true so here, I’m supposed to put what is displayed if d 2 is indeed greater than 59 well, I want it to print on the screen. The word pass. The quotes signify print on the screen. Ok, now I put a comma – and here this is like saying, if not so, let’s back up if D 2 is greater than 59, then print pass.
If not, then print fail and then I’ll put in the parentheses and tap enter on the keyboard and notice what it does Excel tests to see if D 2 is greater than 59, if it is pass and that’s what happened pass so now, I can just use The autofill handle by clicking on eetu, where my formula is and going to the autofill handle I can click and hold and drag down for the entire list of students and we can calculate whether they passed or failed now, there’s an even faster way to do that. I could just double click on the autofill handle and that will automatically deploy it all the way down the column. So this is great right. Well, yes, it is, but what if I want to do more than pass/fail? What? If I want to say, if it’s greater than 92, then it’s an a well, I could go in and say equals if left parenthesis.
D 2 is greater than 92, then print a and I don’t have to put and if not or if false I don’t have to. I could just stop it there or I could continue and say. If it’s not greater than 92, then what would I say pass or fail, or B or C I need more hair. I need to be able to create a formula that calculates more than just whether one statement is true.
You can see the results I’m getting here. They’Re all either false or a that’s all that it’s capable of doing so. Traditionally, the solution to this problem was to create a formula with what’s called a nested-if, but nested ifs can be pretty confusing and pretty hard at first and so in Excel 2019, and also, if you have office 365. Now we have a new solution.
We have a new function called ifs and it really does solve this issue that I just explained to you I’ll just click and drag down and erase the results that were generated with the formula that I created so back up to the top here. Let’S do a similar thing, but use ifs, so I clicked on cell e2, equals ifs left parenthesis and you can see again. I get some help text. It wants a logical test and then comma value with true comma etc.
So, let’s take a look at how this could work first, I’ll start by clicking on D I did before so. If D 2 is greater than 92, then print a and then I’ll put another comma. What, if it’s not greater than 92? If it’s not, then maybe this next logical test is true. Maybe D 2 is greater than 89 and then comma.
If that’s the case, then I want to print a let me scoot over a little bit, so you can see this a little better. What, if that’s not true? Well, then, maybe D 2 is greater than 84, and if it is then print b-plus, I put the close quote well what, if that’s not true what if none of these are true, then, let’s see if D 2 is greater than 79. If it is then print B, so hopefully you’re seeing the pattern here, I’m creating a series of tests. If the first test is true, then the logic stops there Excel will just put an A in cell e2.
But if that test comes up false, then it will look for the next test and see if the results of this test are true if they are a minus, and so it just keeps looking for something. That is true. Give me a minute to finish this pattern and then I’ll resume the video. Ok.
So I’ve finished my formula. Let me just quickly show it to you. As you can see, I’ve established different score ranges for each of the letter grades and notice that after D minus I say if D 2 is less than 58, then it’s an F. Ok! So now that I’ve got my formula with many many ifs in it, that’s why the function is called ifs. All I have to do is tap enter on the keyboard and it calculates the grade for this first person in my list for Gina.
Now I can just double click on the autofill handle and it should calculate the grades all the way down for all of the students. You can see the range of scores and letter grades. This example, I think, demonstrates why ifs is so important and it’s such a great addition to Excel it’s so much easier than a nested-if now watch what happens? What if, let’s say a new student comes in to the class good old John White and we extend the formula down notice that it gives John and F well.
John, hasn’t really taken the test. So let’s say I have not applicable in here or something similar. The formula is getting messed up a little bit, so let’s look at something that we can do to perhaps fix this. It doesn’t always need to happen, but sometimes, when you use ifs, to create a formula you might need to put in one final logical test at the end of your formula.
What if none of these logical tests result in a true? Well, what you can do is you can force there to be a true to do that. Just put another comma in type, the word true put a comma and then in quotes what you want to have a peer if none of these other logical tests are true, so I’m forcing this to be true as a last resort and what I’ll put is tests? Not taken I’ll put that in quotes tap, enter on the keyboard and, let’s autofill, that down but notice. It still didn’t quite work. The reason why, for some reason, the formula is interpreting this as being greater than 92 in excel letters can sometimes be interpreted to have a numerical value, and so that may be why this is showing up as an a. So, even though it didn’t really help us in this case, it is important to know about this true logical test at the end of the formula. Let’S look at a second example of when the ifs function might be helpful here on my second spreadsheet.
It’S an employee list, so I’ve got my employees here. I’Ve got the department that they’re part of here, and I would like Excel to automatically calculate and display who their supervisor is. Yes, I could do this manually, but in the future, new employees will be added I’ll. Put in their department and the supervisor will be automatically calculated and printed on the screen. So how to do that? Well, just like before I’ll start by clicking typing equals ifs left parenthesis B. 2 is equal to custodial. Now, because I’m dealing with a word here, I’m dealing with text, not a number I have to put it in quotes and it’s easy to forget that, but you’ve got to do it. So, if B 2 is equal to custodial in quotes, then print Tiffani as the supervisor. Now, if that logical test is not true, then test to see if B 2 is equal to marketing, if it is then print Adam. If that logical test fails, then test to see if B 2 is equal to customer service, give me a minute to complete this formula and then I’ll resume the video okay. So I’ve finished my formula at least for now, and you can see what it looks like. This is a pretty complicated ifs formula and it’s just going to work from left to right.
It’S going to check to see if this is true, if it it’ll print Tiffani. If it’s not true, it’ll move on to the next one, it’s looking for something that’s true! Now you can see I didn’t put in every single department. So, let’s see what happens when I tap enter on the keyboard to make that formula effective, it found that Alan’s supervisor in the custodial department is Tiffany. Well, let’s autofill this down double clicking on the autofill handle.
Let’S see how it works, you can see. It looks like it’s working pretty well, but look I didn’t put in anything in the formula for human resources and so Excel sees that as an error, and it puts in an n/a error message not applicable and there’s other examples of that here too, and that looks Kind of ugly, it doesn’t look great, so this is a good example of when it can be important to force a true at the end of your formula. So, let’s do that and in order to see this better, I’m gon na have to zoom out a little bit but I’ll just double click on the formula, and so you can see here at the end I’ll just put in a comma and then force a true Statement so true, comma and then I’ll type in not applicable now you’ll notice, true, is not in quotes because it’s not really text. It’S a function. Basically, all right. I’M gon na tap enter on the keyboard. Let’S autofill that down and you can see what happens now.
There are no error messages, it just says not applicable. Now, of course later, I might decide to change that and say something like reports to CEO, so there’s no supervisor just reports directly to the top lucky people, so I can autofill that down and it changes it all the way down. Let’S say a person, switches departments, Todd switches to legal, their supervisor changes. So I hope you can see the great potential in the ifs function and using it in your formulas. You can do something like a hundred and fifty some odd logical tests in one formula using the ifs function. I hope you found this tutorial to be helpful. If you did please like follow and subscribe, and when you do subscribe, consider clicking the bell next to the subscribe button. If you do you’ll be notified whenever I post another video, if you’d like to support my channel, become a supporter of mine through my patreon account and you’ll see a link to that in the description below .