Sign in or start a free trial to avail of this feature.
4. Tiered Pricing with Nested IF
We often want to split our data into different tiers, with pricing a common example. Nested IF statements help us complete this task
Tiered Pricing with Nested IF (00:16)
Our software company has three pricing tiers, based on the number of users a customer has. We want to calculate the revenue for each company, by multiplying price per user by number of users. When we have tiers, nested IF statements can be a good option for a simple structure like this.
Creating a Nested IF Statement (01:08)
We create a column that will calculate revenue using a nested IF statement. There are three possible prices per user: a high price, a mid price and a low price. We write an IF statement that checks if the number of users is less than 100. If so, we calculate revenue using the high price. If not, we create another IF statement that checks if users are less than 500. If this is true, we calculate revenue with the mid price, if not we use the low price.
Improving the Formula (03:19)
When creating the nested IF formula, we hard-coded the limits where companies switch between pricing tiers. We can improve the formula by naming the cells that define the tier limits, and adjusting the formula to refer to these names instead.
This makes the formula easier to read, and allows us to easily see the effect of adjusting the tier limits.
In the previous lesson, we learned how to create lookups using the index and match functions. In this lesson, we'll use nested IF statements to create different pricing tiers. In Excel, we often want to split data records into certain tiers. For example, we might want to capture all temperature readings above 90 degrees or categorize all production times above two hours as late. In these scenarios, lookups are not going to work because we don't want to check for exact matches. We only want to check if a value is above or below certain thresholds. In our software company example, prices operate on a three tier structure. If a customer has less than a 100 users, they pay $50 per user. They pay $47 per user if they have between 100 and 500 users and they pay $44 if the number of users is above 500.
Given this information, we want to calculate the revenue for each company. To do this, we have a number of options. One would be to write a macro, but that's probably overkill given this is quite a simple task. Another option would be to use nested IF statements. Let's start by hiding a couple of columns so that we can see the number of users column and our pricing table at the same time. So I'll select a couple of columns and I'll group with Alt + D + G + G and then I'll hide with Ctrl + 0.
We'll now add a new column and I'll call it Revenue. (typing clicks) And now we'll add our nested IF statement. To make writing these equations a little easier, I've named the three prices high price, mid price, and low price. We'll start by writing our first IF statement and the first logical test will check if the number of users is less than 100.
If this is true, we'll multiply the number of users by the high price.
If it isn't true, we'll create another IF statement.
And this time we'll check if our number of users is less than 500.
(typing clicks) And if it's true, we'll multiply the number of users by the mid price.
And if it's false, we'll multiply the number of users by the low price. (typing clicks) Then we'll close the brackets, and the bracket of the first IF statement and press Enter.
We'll now check our answer, by dividing the revenue by the number of users and the answer is 47 and we're in the correct tier, which is the mid price. Let's do a quick review of the nested IF statement. I started off by checking if the number of users was less than 100. If this logical test was true, we then multiplied the number of users by the high price. In this case the number of users was 309, so it moved on to the next IF statement. The second IF statement checked if 309 was less than 500 which it was, so it multiplied the number of users by the mid price. If this number happened to be greater than 500 it would have multiplied it by the low price. It would be nice if we could check our revenue numbers for different tier values and make our formula a little more readable. So let's start by giving names to the cells that define our tier limits. I'll press Ctrl + F3 to bring up the Name Manager dialog box. I'll press Alt + N for a new name and I'll call this the Lower Limit.
I'll then ESC and move to the next cell. Ctrl + F3 again, Alt + N and call this the Mid Tier Limit.
I'll now ESC and go back to my formula and replace the hard coded values with these new names. So F2 to re-enter the formula and I'll remove the 500 and call it the Mid Tier Limit.
I'll also remove the 100 and call it the Lower Limit.
I'll then press Enter to complete.
We can now adjust our tier limits to see the impact on the overall revenue. As you can see from the formula bar above the spreadsheet, we also now have a very readable formula.
To apply this formula to the remaining cells, I'll copy, I'll skip down to the end, I'll select these cells and Alt + E + S + F to paste formulas.
I'll then move back up to the top with Ctrl + up arrow. Unfortunately, the nested IF statement comes with one big drawback, which you may have already noticed. For each new pricing tier we create, a new IF statement is also needed for the formula. This can get unwieldy for many pricing tiers and Excel caps the number of nested IF statements at seven. If you have this many tiers, it's probably best to build a macro to solve the problem. However, if you have a simple structure, like our pricing model in this example, the nested IF statement is a nice solution. As an exercise, I've created an additional tier for companies with more than 750 customers. See if you can include this tier in our current revenue formula. Also, try changing the formula of the revenue to include the dollar sign and comma separators.