Excel IF Statements Part 3 – Nested IF Statements

August 10, 2016 by Tyrone Pernsley

In this week’s IF statement series, we are covering Nested IF Statements. This convenient formula allows you to return values based on multiple logic tests built into – or nested – in the formula itself. We will explore several uses of nested IF statements in demonstrations below.

Syntax of Nested IF Statements

When you write a nested IF statement, you are essentially saying “If true, do this, if true, do this, if true, do this…if false, then do you this.” You can nest up to seven IF criteria within the formula.

The syntax for nested IF Statements is:

=IF(True-This,IF(True, this,IF(True,this,IF(True,This,IF(False,this))))

Notice: For each new IF statement, you open a new parenthesis, however, the parenthesis closing all come at the end of the formula.

Uses for Nested IF Statements

There are many reasons to use nested IF statements and you will have to determine the best situations to employ them. For the purposes of demonstration, we will use nested IF statements to calculate commission rates based on a sliding scale and salary increases based on years of service.

Calculating Commission on a Sliding Scale

If you remember our Simple IF Statements from last week, we used a single IF condition to return a flat bonus rate or determine No Bonus. But, what if there is a sliding scale? By nesting several IF statements together, you can create a dynamic formula that returns the commission amount based on a sliding scale.

Let’s say you had the following spreadsheet:

You can see the sliding scale in the table to the right. To write this scale into a nested IF statement you would write it as:

=IF(D2<101,”No Bonus”,IF(D2<201,D2*0.03,IF(D2<301,D2*0.03,IF(D2<=500,D2*0.04,IF(D2>501,D2*0.05)))))

Here, what you are saying is: IF the amount in column D is less than 101, return the value No Bonus, IF it’s less than 201, times the amount by 2% (0.02), IF less than 301, times it by 3%, IF less than or equal to 500, times it by 4%, and if it’s greater than 501, times it by 5%.

Here is how that looks in Excel:

As you can see, it automatically calculates the commission payout based on the range of the sale amount.

Calculating Salary Increase Based on Years Employed

Here we have a similar issue. We can see the employee list, their current salary and years employed. We have a table that shows what percentage their salary should increase based on length of employment.

To quickly find the new salary amount, you would write the following IF statement:

=IF(C2<6,B2*1.02,IF(C2<11,B2*1.03,IF(C2<16,B2*1.04,IF(C2<21,B2*1.05))))

Here, we are saying “IF the years in column C are less than 6, then increase the salary in column B by 2% (1.02). IF it’s less than 11 years, increase by 3%, IF less than 16 years, increase by 4% and IF greater than 21 years, increase by 5%. “

Here is how it looks in Excel:

As you can see, there are a variety of ways and reasons to use nested IF statements. We here at Learn Excel Now hope you know feel comfortable applying these functions in your spreadsheets.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel IF Statements Part 2 – Intro to Simple IF Function

August 3, 2016 by Tyrone Pernsley

In Part 2 (get Part 1 Here) of our IF Statement series, we are discussing simple IF statements. These are the basic formulas for returning a values based on your (the user’s) criteria. These functions can be used for a variety of purposes and the examples below explore some of the potential uses of IF statements.

A Definition of IF Statements

IF Statements are logical functions in Excel. When you define the criteria for the statement, you are essentially saying, IF the defined condition is true, then return this value, otherwise (if false) return this value.

The syntax of IF Statements is:

=IF(logical_test, [value_if_true],[value_if_false])

Uses of IF Statements

As you use Excel, you might come across various instances where IF statements will be useful. Below are two examples based on real-world issues where these functions come in handy.

Example 1: Making Purchase Decision Based on Margins

Let’s say you received the following spreadsheet:

Your boss is trying to determine if the company should purchase wholesale products from the new vendor. You have your wholesale price the company will pay and the expected retail price the company can charge. Your boss wants you to find the net gain and profit margin. For any product where the profit margin is 30% or greater, you need mark it “Purchase.” For any product with a profit margin less than 30%, you need to mark it “No”

So, here is your step-by-step process:

Subtract the Wholesale amount from the Retail amount under the Net column
Divide the Whole amount by the Net amount in the Margin column – change number to a percentage
Highlight both cells and use the fill handle to drag the Net amount and Margin percentage all the way down.

Now, at this point, you could manually enter Purchase or No by eyeing up the margin percentage to see if it’s greater or equal to 30%. But you can save time and ensure accuracy by using an IF Statement.

Here is how you would do that:

Under the Purchas column, enter your formula: =IF(E2>=30%, “Purchase”, “No”)

What this formula states is “If the value in column E is greater than (>) or equal to (=) 30%, then return the value Purchase. If not, return the value NO.

Once the formula is entered, you can again use the fill handle to drag it all the way down.

Example 2: Determining Commission Bonus Payout

You received the following spreadsheet:

Your job is find the commission rate of 5%. For any sale over $300, the salesperson receives the 5% sales commission bonus. Otherwise, they don’t get a bonus.

Step-by-step:

In the Percent of Sale column (E), enter the following formula: =d2*0.5
This will yield the 5% sales amount
Copy the formula down using the fill handle
Under the Commission Payout column (F), enter your IF Statement: =IF(D2>300, “E2”, “No Bonus”)

What this formula states is: If the sale amount in column D is greater than 300, then return the commission value in column E, else, state No Bonus

As you can see, there are a variety of uses for IF statements. It really depends on what you need to use Excel for. Coming next week: Nested IF Statements!

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel IF Statements Part 1 – Intro to CountIF

July 27, 2016 by Tyrone Pernsley

IF Statements in Excel are some of the most useful functions you can use. There are a variety of IF functions and each one can be used for a variety of purposes. Over the next few weeks, we will explore the various IF functions, their uses and how to create them. This week, we are focusing on the CountIF Function.

IF statements are often variations on standard functions, but they allow you to return a value based on specified criteria. The normal Count formula (=count(range)) will return the total number of cells containing data within a range. Here’s a quick demo:

As you can see, it just counted the total number of cells with data.

But let’s say you wanted to count the total sales to a particular company. In this case, you could use the CountIF function to find the total sales to a company.

The syntax for CountIF is:

=CountIF(range, criteria)

So, if we’re going to find the totals to Eastern Company, the formula becomes:

=countif(F2:F21, “Eastern Company”)

And that will the return the specified value:

Note: if the value you’re using as the specified criteria also exists in the table, you can select any cell containing that value rather than writing it out:

The above examples are if the value you’re looking for is an Exact Match. You can also do greater or less than.

Let’s say you wanted to find the total number of sales greater than $300. For that you would use this formula:

=COUNTIF(D2:D21, “>300”)

Now in some cases you might want to it count equal to And less than (or greater than). In those cases, you will add the = sign after you enter the greater than (>) or less than (<) symbol.

So, now that we know the number of cells containing values greater than 300, let’s find the number of cells that are equal to or less than 300. That formula looks like:

=countif(d2:d22, “<=300”)

As you can see, this than finds any cell with 300 or less:

We here at Learn Excel Now hope you now feel comfortable using the CountIF Function. We will be brining you more IF statement tips over the next several weeks so stay tuned!

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel IF Statements Part 3 – Nested IF Statements

Excel IF Statements Part 2 – Intro to Simple IF Function

Excel IF Statements Part 1 – Intro to CountIF

Connect with Us

Contact

Connect with Us

Contact

WELCOME BACK!

Reset Password