Transcripts
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.