Search Not Just Numbers

Tuesday 16 October 2012

EXCEL TIP: Look up commission or tax rates from earning bands

Just a short post today on a very useful use for the VLOOKUP function.

Typically when we use VLOOKUP, we are looking for an exact match from a list. For example, we might look up a customer's address, based upon their account code.

But what if we want to look up the correct commission rate or tax rate, based upon what earnings band a particular value falls into?

VLOOKUP handles this extremely well, when you understand its mysterious fourth (optional) argument.

The syntax of a VLOOKUP function is:

=VLOOKUP(lookup value,range to lookup,column to return,TRUE or FALSE)

I you need a recap on using the VLOOKUP function, take a look at my earlier post on the subject.

First of all though, let us outline the problem with an example.

We have a cell (D2) containing an income figure upon which we need to calculate a sales commission.

However, the rate of commission changes depending on the size of the income figure as follows:

0 - 9,999  pays 10%
10,000 - 49,999 pays 20%
and 50,000+ pays 25%

How do we calculate what rate to use?

We could use IF statements, however this falls down with more bands and is very inflexible when it comes to changing the bands. A far better solution is to use the VLOOKUP function.

We need to create a two column table with the start of the band in the first column, and the rate to use in the second. This table must be in ascending order of earnings, i.e.

When we set the final argument in the VLOOKUP to TRUE (or leave it blank), it scans the lookup table for the highest earnings value that is still less than the lookup value, so:


will return the rate we are looking for.

We can also use exactly the same approach for other similar problems, such as looking up tax rates.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Wednesday 10 October 2012

Richard Branson, First Group, The Department for Transport and your Excel Spreadsheet

A major news story in the UK in recent weeks has been the embarrassing U-turn of the government on their award of the West Coast rail franchise to First Group, following complaints from the incumbent, Richard Branson's Virgin.

Full story here

The problems appear to have been caused by unrealistic assumptions on rates of inflation and passenger growth.

This got me thinking about the use of assumptions and variables in Excel spreadsheets generally.

Obviously, any assumptions should be clearly visible and I would suggest (if space allows) above the forecast (or whatever the main content of the spreadsheet is).

I'm sure we all know this to be good practice but it doesn't mean we always do it!

A second, slightly more subtle, rule that I always (try to) follow helps avoid so many of these types of errors.

Make these assumptions an active part of the spreadsheet

What I mean by this is instead of entering the text, say, "Inflation is assumed to be at the rate of 2%", enter the name of the variable in one column and the value in another, e.g.

Then wherever inflation is used in the spreadsheet, always reference this cell (B3 in the example above). This way you always know that what you have stated in the assumptions is what your spreadsheet is using.

You can refer to this cell in two ways, either:

1. Directly, i.e. $B$3 - Note the dollar signs to ensure that if you copy your formula elsewhere in the spreadsheet, it still references the cell, or;
2. As a named range, i.e. name cell B3 Inflation and refer to the range Inflation in your formula.

The second approach makes the formula easier to understand, but the first is usually quicker.

Another significant benefit of this approach is when these rates need to change. I come across so many spreadsheets that calculate VAT (a UK sales tax - currently 20%) on the value in cell A2 as =A2*0.2. When the rate changes, all of the calculations need to be checked, where if the VAT rate had been entered in a cell as above, it would be as simple as changing the rate in that cell!

Simple tips, I know, but many a complex spreadsheet has been brought down by not following them - just ask the Department for Transport!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".