Master Microsoft Excel: Learn the IF Statement Fast!

1. Introduction - IF Statement: Hello there. I'm Jeremy, or as some call me Big dog. Welcome to my Skillshare course on the if statement. Before we jump into the content, let me take a second to introduce myself. I've been in the working world for almost seven years and all of that time has been spent in Excel for my time in college to my current role today, Excel has been a part of my daily toolkit. I've developed a passion for analytics and interest in figuring out the most efficient solutions and a love for automation. My goal with this video series is to get you up to speed with the most foundational formula for any seasoned Excel user. The if statement. The if statement builds you a foundation in Excel logic that will be essential to understanding more advanced formulas down the road. Once you understand the if statement, the sky is the limit for your potential in Excel and other programming ventures. Let's jump into it. 2. IF Statement Basics - Part 1: Hi everyone, Welcome to the first video in the series. In the upcoming videos, we'll be covering the if statement. The if statement is the bread and butter of every Excel users toolkit. It allows us to return different results in a variety of different formats based on the outcome of logic statements that we provide. Mastering the if statement is foundational to your learning with Excel as more complex formulas rely on it to be fully effective. Within the examples in this lesson, I will walk you through the different parts of the if statement. And by the end of the lesson, you'll be well on your way to mastering it. Let's dive in. In this example, we have a list of people attending a birthday party. We want to figure out how many adults are attending so that we can plan for food and drinks appropriately. We can use the if statement to help with this problem. The if statement will test if the person is over the age of 14 and if they are, it will output the number one. If there are exactly 14 or younger, the formula will output the number 0. Once this formula has been applied to all the cells, will be able to sum the count of adults column and know how many adults to plan for for the party. The if statement starts with the logical test. Is the age listed in B5 greater than 14? If the age is greater than 14, meaning the logical test is true. What do we want the formula to return? We want the formula to output the number one. Now, if the age is equal to 14 or less than 14, meaning the logical test is false. We want the formula to output the number 0. As we do not want these guests to be counted. Cool, we got the desired output in the first cell. Let's copy this down to the other cells. Out of the 50 guests, we are getting a total of 24 adults. A few quick tips that I want to mention. To start a formula. You can either use the equals or plus characters. I prefer the equals as the Plus seems to crowd the formula bar when the formulas get more complex. Also another tip. Once you start typing the formula, you can arrow up or down to find the formula and use the Tab key to select the formula. Let's move on to another example. 3. IF Statement Basics - Part 2: Hi everyone, Welcome to the second video in the series. In this video we'll be going over the second example for the if statement. Let's dive right in. In this example we have a list of invoices from February and the customer that each invoice belongs to, we would like to give back to the customers that purchase the $100 or more in the form of a 10% rebate. We can use an if statement to help us solve this problem. The if statement will check to see if the invoice is equal to or greater than a $100. If the invoice is equal to or greater than a $100, We will multiply the invoice by 10% to get the rebate dollars owed. If the invoice is less than a $100, we will return 0 since the customer did not hit the a $100 rebate level. Like in the previous example video, the if statement starts with the logical test. Is the invoice amount in B7 greater than or equal to $100. If the invoice is greater than or equal to $100, meaning the logical test is true. We want to multiply the invoice dollar by the 10% rebate in cell B3. One quick thing to mention, we want to make the column and row of cell B3 and absolute reference, which means that if we copy the formula to other cells, B3 will stay referenced. To make a row or column and absolute reference, you would put a dollar sign in front of the column or row that needs to stay absolute. When the cell is highlighted, you can use the F4 function key shortcut to cycle through the cell reference types. Now back to the formula. If the invoice is less than a $100, meaning the logical test is false. We want to return 0, which means there'll be no rebate to ensure that our formula works properly. Let's use cell D7 as a double-check. In D7, let's take the rebate dollars divided by the invoice dollar. For invoices greater than or equal to $100, we should see 10%. And for invoices less than a $100, we should see 0%. Let's copy these formulas down to the other cells. Awesome. We are seeing the result we expected. Let's move on to the next example. 4. Nested IF Statements: Hi everyone, Welcome to the third video in the series. In this video, we'll be going over the third example for the if statement. Let's get into it. Just like in the previous video, we have a list of customer invoices from February. The customer that each invoice belongs to. This example, instead of only providing the customer with one rebates here, a 10% rebate at a $100, we will have three, uh, 10% rebate at $100.5% rebate at $50 in a two-and-a-half percent rebate for any invoices below $50. This exercise, we'll also introduce us to a new concept, nested if statements. For nested if statements, the true or false outputs of the original if statement can be replaced with another if statement. To better illustrate this, let us look at the logic tree below. As you can see, the logical test of the first if statement starts out like we're used to. But when the formula picks either true or false, we have to provide a logical test and true or false values the second or nested if statement. Feel free to pause the video to think through this. This is a tough concept and may take some practice to understand. Let's move back up to the formula and work through this example. Like we're used to. The if statement starts off with the first logical test. Is the invoice amount in B7 greater than or equal to $100. If the invoice is greater than or equal to a $100, meaning the logical test is true. We want to multiply the invoice dollar by the 10% rebate in cell B3. Don't forget to make cell B3 and absolute reference. If the invoice is less than a $100, meaning the logical test is false. Since we have two options left instead of one, we will have to start a nested if statement that allows us to choose how we get to the 5% rebate and two-and-a-half percent rebate scenarios. The logical test for this if statement will be is the invoice amount in B7 greater than or equal to $50. If the invoice is greater than or equal to $50, we want to multiply the invoice dollar by the 5% rebate in cell B4, again, making cell B4 and absolute reference. If the invoice is less than $50, meaning the logical test is false, we want to multiply the invoice dollar by the two-and-a-half percent rebate in cell B5, making this cell an absolute reference, as well as with our previous example to ensure that our formula works properly, Let's use cell D7 as a double-check. In D7, let's take the rebate dollars divided by the invoice dollar. For invoices greater than or equal to $100, we should see 10% for invoices less than a $100, but greater than or equal to $50, we should see 5%. And for all cells below $50, we should see two-and-a-half percent. Let's copy these formulas down to the other cells. Great. We're seeing the result we expected. That wraps up the if statement example series. Let's move on to learning the next formula. 5. IF Statements with AND Formula: Hi everyone, welcome to the fourth video in the series. In this video, we will be going over the last example for the if statement. Let's jump in. Just like the second video in the series, we have a list of invoices from February, the customer that each invoice belongs to. In this example, however, we would like to give back to the customers who purchased a $100 or more and have a contract with our company. We can use the if statement coupled with the end formula to help us solve this. The if statement will first check to see if the invoice is greater than or equal to $100. Next, as part of the n formula, we will check to see if the customer has a contract with us. Like in the second example video, the if statement starts with the logical test. But in this example we have two logical tests. We need to add in the end formula. Now let's begin. The first logical test is the invoice amount in C7 greater than or equal to $100. The second logical test is, does cell B17 contain yes, meaning that they do have a contract with us if the invoice is greater than or equal to a $100 and the customer has a contract with us. We want to multiply the invoice dollar by the 10% rebate in cell B3. If the invoice is less than a $100 and or the customer does not have a contract with us, we will return 0 as we've done in previous videos. Let's add in a double-check in cell E7. Take the rebate dollars divided by the invoice dollar. Let's copy both formulas down. For all invoices greater than a $100 and having a yes in the contract column, we should see 10%. For all others, we should see 0 suite. That is exactly what we see. Since this is the last video in the course, I'd like to thank you all for your time. You have been great students and I wish you the best on your Excel journey or business ventures. If you're ever feeling stuck, feel free to stop back and ask a question. The big dog will always be here to help.