All 3 Excel IF Statements + how to AVOID PERFORMANCE ISSUES!

the if function is essential for all excel users to master but i see it used incorrectly too often in this video i'm going to briefly cover if and then go into more detail on nesting ifs because this is usually where people get stuck and i'll also cover the newer ifs function for those with excel 2019 or later or those with the microsoft 365 license i'll also cover the common mistakes and what to use instead let's start with a quick look at the if function the if function takes three arguments the logical test the result if the logical test is true and the result if the logical test is false for example let's say we want to classify these items as do or not due therefore the formula is if today's date and we can use the today function to automatically return today's date minus the loan date is greater than or equal to 90 90 days then we want it to return the text due notice we wrap the text in double quotes if this was returning a number we wouldn't need the double quotes otherwise i'm just going to leave it blank to indicate that it's not due press enter because it's in a table it's automatically copied the formula all the way down we can see the formula here on the right alternatively if we wanted to return some text in here for example we might like to say not due we can insert text or again a number although it wouldn't make sense to put a number in here okay so that's a quick example of a single if formula let's take a look at nested ifs now nested ifs allow you to handle more logical tests and return different results accordingly for example let's say loan items greater than 90 days old are classified as overdue items equal to 90 days old are classified as due and items less than 90 days old are classified as not due again we start with if and then today so today's date minus the loan date if it's greater than 90 then it's overdue and because this is text we're wrapping it in double quotes and then in our value of false argument we simply enter another if for our next criteria so again today's date minus the loan date is equal to 90 then it's due and then if that's false everything else is not due so all we need to do here is enter our value of false close my parentheses on my first if and my second if press enter and it copies it down now it's essential that you get the order of the logical tests in a nested formula correct because as soon as a logical test returns true the formula stops evaluating it's not so much an issue with this formula but it is a common mistake i see people make and they wonder why their formulas only return the first result now if you have excel 2019 onward or a microsoft 365 license then you have access to the new and easier ifs formula let's take a look the if's formula simplifies nesting ifs for example we can write exactly the same formula in groups of logical tests and then the result if true so if we take a look you can see ifs accepts a logical test and then the value of true and then we can just repeat that pattern so let's do it we say today's date minus the loan date if it's greater than 90 then the value of true is overdue and then we just enter our next logical test so again today minus the loan date is equal to 90 then it's due and lastly because there's no value of false argument in the ifs formula we skip the last logical test and we simply enter true and then we enter the value that we want if all the previous logical tests are false so in this case not due close my parentheses press enter we get the same results but the formula is easier to write now i should point out that just like the nested if's formula if's also stops evaluating once a logical test evaluates to true so remember to get your logical tests in the correct order now for a relatively easy formula like if i sure see a lot of mistakes the most common is getting the order of evaluation wrong which i just mentioned the next most common mistake which i think is worse is too much nesting in excel 2007 and later you can nest up to 64 if functions and the newer ifs function can take up to 127 logical tests but just because you can create monsters like this which is a real formula shared with me by one of our members doesn't mean you should now aside from the fact that a formula like this is very difficult to read and understand for anyone inheriting the workbook let alone the person who wrote it the main problem you're likely to encounter are performance issues now the first if function evaluates to true then it's okay excel stops at that point and returns the result and the remaining 21 ifs don't get evaluated however the cells where every one of the if formulas evaluates to false consume a huge amount of processing power just think about the work excel has to go through to get to that last if it has to evaluate 22 if functions 13 and functions 19 left functions one is number one match one if error and one vlookup and that's just in one cell now multiply that by a hundred or a thousand or tens of thousands of cells and it doesn't even have to be the last if that's the problem any more than two nested ifs in a large workbook can slow your file down so if you're wondering why your workbooks are large and slow take a look at your nested formulas often the alternative to an sdf formula is a simple vlookup or if you have microsoft 365 you can use x lookup for example let's say we want four status categories with new loans classified as 0 to 45 days old impending from 46 to 89 and so on we could write this with a nested if or an f's formula and that's what most people do but the more efficient option is to create a lookup table like i have here and use vlookup or xlookup to find the correct status let's look at vlookup first the first argument is the value that you want to look up so here we'll calculate the age so today minus the loan date and we're looking it up in this table here we want to return the value from the third column of the table and the last argument here is normally false or zero for an exact match but with this lookup we use true or one i'll put through in and this is going to return an approximate match because we don't have every possible age listed in the lookup table let's close parentheses on vlookup and you can see it's now returned the relevant status and if you want to verify here's the days old that i inserted in this column just so that we could cross-reference to the table for the purpose of this lesson now i should point out that vlookup only uses the from column in the lookup i've included the two column for completeness so it's easy to follow the example but it's not required the way vlookup works when the last argument is true is to find an exact match or the next smaller item and it's therefore essential that this list is assorted in ascending order based on the from column let's take a look at using x lookup for those who have microsoft 365 licenses so x lookup again the lookup value we're going to calculate with the today function minus the loan date where are we looking it up in the from column what are we returning the status if the value is not found i'll skip that and the match mode here is going to be minus one we want to find an exact match or the next smaller item close parentheses on x lookup press enter we get the same results just a different function again with x lookup the lookup table must be sorted in ascending order to use the match mode minus one that we have here i hope you found these functions useful you can download the file for this lesson from the link here if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 35,612

Rating: 4.9958334 out of 5

Keywords: if function, if formula, if statement, nested if formulas

Id: 0zTeXujtOZI

Length: 9min 0sec (540 seconds)

Published: Wed Jun 23 2021