Excel IF Function. Everything You Need to Know. Excel Magic Trick 1634

Excel if function everything you need to know in one video now this is gonna be your one-stop shopping for how to use the if function these twelve examples from a logical formula to bonus Commission and over time if function formulas will see a great income statement formula how to use the or function inside of if the and function inside of if we'll look at nested ifs we'll look at a really bad use for the if if array formulas and of course is functions too now oftentimes people think hey the if function is hard it's not hard at all and we'll start off with a very simple example that illustrates the essence of what the if function does if this is the hurdle in order for you to get seven hundred and fifty dollars bonus looking at this number because it's less than the hurdle we need a zero in the cell but this sales number is greater than the hurdle so we need seven hundred and fifty bucks in the cell anytime you have one of two things to put in a cell that's when you use the if function now before we use if we got to talk about what a logical formula is a logical formula simply delivers true or false for our formula in this column we want a formula that delivers true or false based on what the contract reads it says if an employee has sales of twenty thousand dollars or more they get a bonus of seven hundred and fifty now for our formula in our first example we don't want the zero on the 750 I just want to show true or false now before we can build logical formulas we got to go over to the sheet comparative operators and there's our six comparative operators we can ask if something's equal greater than greater than or equal to less than less than or equal to and not you can use this table to figure out what the words mean and which comparative operator to use back over on sheet number one we're gonna build our four equal sign left arrow to get as a relative cell reference the sales for each row and now I need to ask the question of those sales are you greater than or equal to and I click on the hurdal now when I copy this formula down I need it locked on the hurdle so I hit the f4 key now the reason that we had to use greater than or equal to is because our boss is nice if we get exactly 20,000 we actually get the bonus that's the equal sign everything greater then we'll also get the bonus now because we used a comparative operator when we control enter to put the formula in the cell and keep the cell selected we will always get either true or false now I'm going to point to the fill handle in the lower right hand corner and when I move my selection cursor on top not that cursor that's the move cursor I want the crosshair or angry rabbit cursor now I can double click to copy that formula down now I'll go to the last cell and hit the f2 key I'm verifying that the relative cell reference the blue cell reference and the absolute Orlock cell reference the orange one are pointing in the correct location and they are that type of logical formula is what we're going to use inside the if function to deliver either 750 or 0 enter now let's click on sheet number two now what we really want is 0 or 750 so now we use equals and the if function now there's 3 arguments the logical test that's where we put our formula element that delivers true or false then the remaining part of the if is easy what do you want to put in the cell if it's true what do you want to put in the cell if it's false so left arrow and we're gonna do the same logical test because our contract says 20,000 or more so I click on the hurdal f4 now I type a comma value of true wants to know what the formulas should put in the cell if it's true well there it is 750 I hit the f4 key to lock it comma now the value false argument because this number will never change it's a zero I'm simply gonna type it into the formula close parentheses control enter double click and send it down in the last cell I hit the f2 key I'm verifying that all the cell references are pointing in the correct location and they are now if the bonus was actually 950 I change it hit enter and everything updates now in this case we put one of two numbers into each cell example number three we want to put one of two text items into each cell here's the total data used and the hurdal is 1 and we want to put either over the hurdal or under but the contract reads if your data is under one gigabyte per month you do not pay extra now we're not worrying about the paying extra we just want to put one of these two text items so in the top cell equals if the logical test well I'm looking at this relative cell reference and I have to ask the question are you less than this hurdle right here f4 key that comes out true or false now I type a comma the value is true well it's gonna be our value under f4 to lock it comma the value of false oh you're over if the f4 key closed parentheses control enter double click and send it down and the last cell hit f2 our goal was one of two text items no problem the if function can do that too now in sheet number four here are the employee sales and the contract says if sales are more than 30k you earn a 5% bonus otherwise one percent this is the true value this is the false there's the hurdle and 30000 is not included it says more than equals the if function we need to look at our sales are you greater than without the equal sign our hurdle f4 key comma the value of true that's our point zero five f4 to lock it comma the value of false while you still get one percent f4 close parentheses now this is going to deliver one of two numbers to the cell control-enter double click and send it down I'm verifying but that's not quite what we want so I come back to the top cell and f2 my cursors at the end notice the if is delivering one of two numbers to the cell but if we times and click on the sales now it's simply delivering one of two numbers to the formula which is exactly what we want this will give us our Commission amount control enter double click and send it down and that is looking good so in this case we're delivering one of two numbers to a formula by the way don't get tricked here if you click in the cell lookup in the formula bar that is not 30,000 whereas this one is not 30,000 either if we highlight these numbers go up to the number group and increase the decimals one two we can see that the number formatting was displaying the numbers but the underlying numbers are what these two formulas acted on so the formulas calculated 300 and 1500 correctly now let's go to example number five this is a payroll example we have to compare weekly totals to the number 40 and ask the question hey total hours are you greater than 40 f4 to lock it if that's true comma then we need to put a 44 total regular hours f4 comma otherwise all we need is the weekly total close parentheses control enter double click and send it down after verifying we come up into overtime hours and we say hey give us the total - whatever the regular hours are ctrl enter when I copy this down anybody who's not past 40 gets a zero otherwise we have the correct overtime hours now let's go to sheet 6 in this accounting example we want to put one of two text items into a cell either net loss or net income the logical test we're gonna check total expenses are you greater than revenue comma and in this case the texts that we're putting in the formula will never change so we're gonna hard-code it into the formula net loss in double quotes that's the value of true otherwise net income in double quotes close parentheses when I hit enter right now it says net income but if this value actually was 49,000 I hit enter and now it properly says net loss example seven and eight sometimes it's the case that we have two logical tests if our goal for each customer is to check whether at least one of these credit ratings are past the hurdle we can use the or function in logical one we check credit rating and in this case the contract says greater than or equal to the hurdle F for now I type a comma logical - there's the second credit rating greater than are equal to the hurdle F for now the or function runs an or logical test which means it looks through every single logical test you enter and as soon as it sees one or more Truths it automatically delivers a true to the cell so I close parentheses control enter double click and send it down in the first row we can see false true so of course or delivers it true in the second row we get two truths or delivers a true it's only when there's false false meaning all the logical tests are not to be false that the or function delivers a false now we put the whole or function in the logical test argument comma and then we put value of true comma value of false then when we enter it and copy it down we have a formula that tells us which customer gets credit and which does not now sometimes we want two logical tests but we want to check if both logical tests come out true in that case we use an and logical test just like the or function the an function allows you to put as many logical tests as you want we're gonna check if last year sales are greater than the hurdle the contract says it must be past the hurdle f4 comma and our second test is asset value are you the hurdle of 300 kf4 close parentheses control enter double click and send it down the only two records that get it true from the and function are when both values are passed the hurdle then we place the and function inside the logical test of the if then put your value of true value of false when we enter it and copy it down now based on a completely different rule we have customers that get extended credit and ones that do not get credit based on an and logical test now on cheat number nine we have a payroll calculation where in this cell we need one of three things if your cumulative pay before this paycheck is already greater than the hurdle then none of your earnings are taxable if your cumulative pay before the paycheck was less than the hurdle and then after the paycheck it was bigger than the hurdle then only part of this week's pay is taxable and then the final situation is if your cumulative pay after this paycheck is still below the hurdle then your whole paycheck is taxed when you have three or more things that you want to put in the cell then you want to consider a nested-if formula we'll start out our formula with a logical test I'm just gonna check hey before this paycheck have we already passed the hurdle f4 comma well we know the answer to that it's zero now we type a comma any time you get to value if false and there's two or more items remaining then we have to type another if now I check the cumulative at the end are you greater than the hurdal f4 if that's the case that means we just jumped over the hurdal and comma the value of true is hurdled minus whatever the cumulative pay was before this paycheck otherwise please give us the whole paycheck now I see this f8 I have to f4 to lock it now when you come to the end you keep typing parentheses until you see the black one and you know you're done control enter double click and send it down I'm gonna go to the last cell and hit f2 now notice we had three items that needed to go into a cell I'm gonna hit enter we're gonna go to our next example 10 there is a nested-if function that you don't want to ever use the nested-if is checking through each one of these items but really when we're given a table like this this is the formula we want to do vlookup if we're doing approximate match like 4 units you want to consider using lookup now example number 11 now in Excel we have a bunch of great built-in functions for making aggregate calculations the average IFS averages the numbers if the product is quad there are also aggregate calculations for counting and for adding if a particular condition is met the problem is not all aggregate calculations have an equivalent ifs function for example there's no standard deviation ifs well we can use our standard deviation function and right inside the number one argument we can type out the if function inside of logical test we build a construction where we ask how many of you in that column are equal to the condition and for value of true we put the numbers that's it we don't put anything into false close parenthesis on the if and right inside of number one when I hit the f9 key you can see that the if function has gotten the three numbers we need for the quad product control Z and at the end I'm going to close parentheses now this is a special type of calculation called an array calculation where we're looking at a full column and you have to enter this formula using the special keystroke control shift enter and you have to verify that Excel put it in automatically those curly brackets then you can copy the formula down and verify that it's working in the last cell here we used an array calculation and the if function to pick out only the numbers that meet a specific condition in our last example we need a checkbook balance formula but when nothing's enter here I don't want the formula to calculate in the logical test I want to check using the is functions now there's lots of different is functions the one we're gonna use is is number we're gonna check whether someone has entered a date which is a number that comes out true or false for all of these it comes out true but down here it comes out false comma well what do I want in the cell if it's a number I want to calculate the balance previous balance - whatever's in the subtract column plus what's ever in the addition column so we're putting a formula in value if true comma otherwise I need to show nothing and the syntax for that in an excel formula is double quote double quote with nothing in between that actually is considered text with zero length but for us when I close parentheses control enter copy it down down here it's showing nothing but as soon as I put a date and hit the tab key my formula shows up when I enter the subtract number by hitting Enter the formulas update using the is blank here's another way we could have created this formula we also could have used is text all right that was a lot of fun with if function 12 awesome examples in your one-stop shopping if function video hey if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from XLS fun hey and if you want to learn more about the index function or formulas check out these videos [Music]

Channel: ExcelIsFun

Views: 13,743

Keywords: Excel, Highline College, Mike Girvin, excelisfun, excel is fun, excel fun, excel magic, Excel Formulas, Excel Functions, formulas and functions, Excel Magic Trick 1634, EMT, EMT 1634, if formula, if function, excel if formula, excel if function, If Formula, Excel If Formula, Excel If Function, Comparative Operators in Excel, Nested IF Formula, Nested IF Function, IS Functions, AND Function in IF Function, OR Function in IF Function, Excel OR Function, Excel AND Function

Id: CutG8B1Dudk

Length: 17min 24sec (1044 seconds)

Published: Wed Jan 22 2020