10 Advanced IF formulas every analyst should know

back in 2004 when i was doing my mba program i spent a whole afternoon trying to figure out why my if formula wouldn't work now let's avoid that pain for you in this video i will teach you 10 advanced examples of a formula watch it until end for some extra bonus tips let's get in for the purpose of this if advanced tricks we will use the employee data that i'm showing here the first trick that i have for you is only one-off condition let's say you want to identify all the male employees or those who have salary of under 85 000 but you don't want to identify people who meet both of those conditions you want to get the people who are id satisfying only one of those conditions such a thing is called exclusive or and you can use the if function along with the xor function to get what you want so you will say if xor gender is equal to male comma salary under 85 000 and then whatever you want as an output so for example i would like to include them so i will point to h4 and make it an absolute reference same with exclude absolute reference to make something an absolute reference you can just point to the cell and then press the f4 key excel will add the necessary dollars for you just drag this down and you will find people who are meeting only one of those conditions so for example if there is a male employee who is also having less than 85 they will be excluded here is one example where andrea is both male and less than 85 000 they are excluded whereas this person they are male but their salary is not under 85 000 so they are included that's the number one number two is doing calculations where you want to check if any two out of three conditions are met this is a very useful in many situations and you cannot directly write an end or or formulas to solve this so i'll show you one simple way of doing this here i want to find out which employees are meeting any two out of the three conditions so what we will do is we will say if and then open bracket check for the condition so we'll say department is equal to website right that's the first condition then i'll use the plus option and date joined is equal to 2009 we only have the exact date not the year so we will use the year function on the date joined is equal to 2019 then plus salary greater than 90 000 now what we are doing here is we are taking three logical checks department is equal website year is equal to 2019 salary greater than 90 000 and we are adding them with the plus option so normally this check alone will give you a true or false value but when you add such true or false values with plus option what excel will do is it will take the true value convert it into one if there is a false value it will convert it into a 0 and perform an arithmetic operation so this will be let's just say this person is in procurement so that will be 0 and they have joined in 2019 so this will be 1 and their salary is not more than ninety thousand so that will be zero so it will be zero plus one plus zero net result is one so now all i have to do is check whether that value is two right you could check directly is equal to two or if you want to see whether to include anyone if they are meeting any 2 out of 3 or 3 out of 3 then you can simply say greater than or equal to 2 then you can say they should be included else they should be excluded so this will automatically identify all of that now here is a bonus trick instead of dragging the formula like this you can simply select the first cell in your formula series and go to the corner when your mouse pointer changes into that dark colored plus icon just double click and excel will automatically fill this down number three is an alternative to the between formula normally when we want to check for between condition we use the and function and check both edges so let me show you how that will work we would say something like if i want to review all the employees that have joined from 1 jan 2019 to 30 june 2019 so we'll say and date joined is greater than or equal to a date of 2019 1-1 and then date joined is less than or equal to the date of 2019 6 30. if that condition is met we would like to have a review with them else blank and you can just fill this down and you will identify people who need to be reviewed this is the usual way of doing the between condition but here is a shorter and another way of doing this you could use the median function to check for between conditions so you would say median your date and then the starting date it could this will work for both dates and numbers so you'll say date of 2019 1-1 and then the ending date is 2019 6 30. you can type these or you can get them from another cells and all you have to do is use the median and use the median same as this date if so they need to be reviewed as not and we can just fill this down it will give you the exact same results but this formula is not using the end function it is using the median function to check for whether this date happens in the middle of those two other dates i leave it to your imagination to figure out why this works let's go to the number four here is a place where we normally use nested if functions and i'll show you a substitute for nested functions so we want to calculate employee bonus based on some rules the bonus rule is like this we want to give one percent bonus for all the websites website staff three percent bonus when for the staff that are in the sales department and joined in 2018 everybody else will get two percent now we would normally write the if function and nest it with another function or two of them to pro come up with the bonus calculation for this but do you know that you can use the ifs function this is a new function introduced in excel it works in excel 365 and excel 2019 and you can use this to reduce the length of your formulas and make them more readable so i'll show you how the if function would work if department is equal to website the bonus is one percent then the next one is if they are both in sales department and joined in 2018 so we'll say end off department is equal to sales year of date joined equal to 2018 now both of those conditions are met they should get three percent else they should get two percent so to say else in if's formula there is no direct else option it will always have a logical test and a value thing going on so we'll simply say true and two percent this will give you the answer that you want in a very simple manner the formula is much more readable than a bunch of if formulas that are nested inside each other because they tend to look very complicated whereas this will be easy to read and maintain let's see how well you are understanding the if formula if you are enjoying this video so far give it a thumbs up and continue watching for more good content to come now for number five i will show you another way of calculating the same without using any if formulas this is called boolean replacement trick wherein whenever you are calculating a number that is a result of one or more multiple if conditions you could use the boolean replacement logic to calculate those numbers directly instead of the if formula so i'll show you how this will do this will be done the way to do this will depend on each individual situation so you'll have to think a little bit but the advantage of boolean replacement over regularly for ifs functions is it tends to be a little faster and it will uh it feels more natural once you get used to it so let's get into this so what we will do is everybody will get two percent and then if you are in website you get one percent less than that so that is one percent and if you are both in sales and joined in 2018 you get one percent extra so we'll start off with two percent plus well technically we could also say minus and then open bracket department is equal to website times one percent so two percent and then if you are in website department this will be true two times one percent true times will become one times one percent so one percent otherwise it will be zero because if you are not in website department it will be false for you so this point will will not be anything so you'll still keep your two percent right now we need to simply check if you are in your sales and joined in the year 2018 so we'll say and this is equal to sales and year of your date join is 2018. now if both of those conditions are going met you will get another percentage so that times one percent and we'll press enter double click we'll get the answers now this answer will be exactly same as the other function everywhere but this formula doesn't have any function going on because we have taken the if function and turned that into a simple arithmetic operation of plus and minus and calculated what we want the next advance the formula tricky is to check if an employee is part of the on-call support team so this is basically a list check kind of an operation where i have an employee here and a list of staff that are on call and i want to just check if the person here is also appearing on the other list here is how you would do that if and then you would use one of the counting functions so i'll use the countifs function point to my range that has the on call support team make sure that this address is absolute reference so we'll select everything press f4 that will add the necessary dollars around it and comma this person right and then we will say on call not on call like that you could also use one of the lookup functions to do the same but i find the counting function is better and then we'll fill it down now you might be thinking shouldn't we be checking if this count is greater than 0 or is equal to 1 well not necessary because remember the logical test will have to be just come up with the true or false value what does the countifs function come up with it will come up with 0 or 1. now technically 0 is false and 1 is true so we do not have to specify any extra condition here just writing the count of salon is sufficient because if the count is 0 excel will treat that as false and go to not on call automatically so i hope you found both the list check as well as that extra idea of not having to use the comparison operators as a bonus trick useful the next one is array if formula let's say you want to calculate the median salary of all the website staff now if i just want to calculate median of all the staff i could use the median function select my staff and get the answer but what i wanted is not this i want to have the median of those people who are in website department if we were we are interested in average of such people we could use the average ifs function but unfortunately what we need is median and there is no median ifs function right this is where the if formula can work all you have to do is say median and then write the if function inside if select the entire list of departments is equal to website and then point to the salary close the bracket close it again notice what this if function does it is checking a bunch of values against website so this part will return a bunch of true or false values and wherever it is true this will give the corresponding salary wherever it is false we have not specified what to get so it will simply be false and all of those values will go to the median function and get the answer let's first see the number and then go and understand how that works it will come up with the answer now let's just see what happens for this internal part alone i can select that and here is a bonus trick for you just select the portion of formula that you want to see the result not the whole thing just this bit alone and press ctrl equal to you could also use the f9 key it will give you the answer for that part alone this is very useful for understanding and debugging things as well and i can see that it is false wherever the person is not in website so this person is in procurement so that's false and it gives me a couple of website salaries here again false false go so on and so forth and when the median formula reads these values it ignores all the false values and it only pays attention to the number and then it will calculate the median for that so that's how that works now a quick note if you are using excel 365 you can just type this formula press enter it will work in older versions of excel you would need to press ctrl shift enter to get the correct result time for our eighth advanced if formula trick this is where i want to check if the name contains the letters bo anywhere basically a wild card based search there are many ways to do this i'll show you my favorite method of doing this feel free to comment and share your own technique of doing this as well so i'll say if and then i'm using the countifs function because this function is aware of the wildcard setup and then i'll say this is my range and the criteria is that so i'll simply say star ampersand that now that needs to be absolute reference and another star so i'm saying anything bo anything that's what we are looking for count for that and if so we'll simply say bo person else not a bow person and it will give you the answer wherever the person has bow in it like this person has bow chest bonnell has bow and everybody else will be not poor persons so that's the wild card one now these eight tricks that i've shown you are writing a formula the next two tricks that i will show you will teach you how to use if formula or the logical construct in other situations so the number one that number nine i have for you is using conditional formatting to highlight people that are in a specific department and gender so we'll quickly set up an input area for both department and gender and you can give whatever value you want here so i'll say website mail and based on these two conditions i want to highlight the staff here so we go select all of this go to conditional formatting new rule and let's quickly write a formula basic rule we will say now you might be tempted to write if department is equal to this and this but you don't need to write the if formula here for the conditional formatting to work all you have to do is come up with a boolean or a logical expression that will evaluate to true or false so i will simply say and select the top department cell make sure the reference is not in this format but it is locked to column e so dollar e 50 is equal to that comma gender is equal to this so we construct the end statement like this it might look a bit complicated but have a read at this once again and it will make sense we go to format select a fill color let's select something nice and bright and make the font bold as well so it will highlight all the mail employees in website department and those alone now i can change this i can say sales i can say female i can say finance and i'll get what i want so that's how you can use the if or the boolean logic inside conditional formatting that's our number nine now we are nearly at the end number 10 and i want to highlight any employees that are making more than the average salary of all the staff right so this is where let me just uh place it here and anybody whose salary is more than the average of all the staff we want to highlight so we'll go to the data here we will add highlight and simply say if this person's salary is greater than average of all the salaries now that range needs to be absolute reference because when you drag it down it wants to still look at g50 to g65 then i want their salary else i want nothing n a once all of this is there time for another bonus trick just quickly add this to chart you can right click go to select data and edit but here is a tricky easy way to do this select your data copy select the chart paste ctrl v it will add the data to your chart and it will also now show two bars for someone who is making more than average select the second bar format it you can format with control 1 by the way set the overlap to 100 so that the color will go and sit on top of this let's select the other one and quickly make it this color so that above average salaries are highlighted in dark orange and regular salaries are highlighted in dull orange color there you go 10 advanced if formula tricks i hope you found all of this useful and interesting and i hope you found the bonus tricks that are peppered throughout the video helpful as well if you need a hand just go and grab this file the file link is in the video description and check it out but if you would like to learn more about conditional formatting check my video that is linked on the screen that talks about five of my favorite conditional formatting tricks see you again in another video bye

Channel: Chandoo

Views: 85,636

Keywords: chandoo, chandoo.org, Excel, spreadsheets, excel formulas, microsoft excel, advanced excel, excel advanced formulas, excel for analysts, advanced excel tricks, advanced if formula, if function tips, nested if, multiple conditions in if formula, how to test for exclusive or in excel, between condition excel, two out of three conditions with excel, median if formula excel, advanced if formula explained, excel tips and tricks, excel tips, excel tutorial

Id: -yFpzIRifK4

Length: 21min 54sec (1314 seconds)

Published: Wed Mar 31 2021