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
Nested IF statements
- Allow you to separate data into different tiers
- Work well when you have only a few tiers of data
- The more tiers you have, the more IF statements you have and this does get unwieldy
CTRL + SPACE: Select column
ALT + D, G, G: Group selected columns
CTRL + 0: Hide columns
ALT + I, C: Insert column
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
F2: Jump inside formula
F4: Anchor cells (when inside a formula)
CTRL + F3: Open the name manager
ALT + N: New name
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 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 that 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 will now add a new column, and I'll call it Revenue.
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.
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.
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 are 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 multiply the number of users by the high price.
In this case, the number of users was 309, so it moved down 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, I would’ve 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 and the overall revenue.
As you can see from the formula variable of 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, then 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 format of the revenue to include the $ sign and comma separators.