Gideon's Excel Tips, no. 3: Display a warning message when a target is missed

In many situations it is helpful if our Excel models draw attention to certain conditions, like going over budget or missing a target of some kind. This can be done using Conditional Formatting but we can also use the IF function to display explicit messages that explain what has happened.

In this example I have six months of sales data from nine salespeople and each of them has a 6-month sales target of $20,000. I want to display a message next to each person’s 6-month total that draws our attention to the figures that are under the target.

1.    Enter the target into a cell and name it

I want to compare the 6-monthly totals for each person to the value of the target in my IF formulae. Rather than hard-coding the target value into each formula, which would cause lots of problems if the Sales Director decided one day to change the target, I’ll write the target value into a chosen cell and enter references to that cell into my formulae.

To make the formulae nicer to read I will also give a Name to the cell containing the target value. (If you haven’t used Names before, they are great! They are the nearest that non-programming Excel users get to using variables, like in programming.)

My target value is in cell K4. To name this cell (the easy way – there is a slicker one, but it takes too long to explain for a Quick Tip!) click in cell K4, click in the Name Box and type the name, making sure not to enter a space in it. (Names can't contain spaces.) Press Enter.

Cell K4 now has the name SalesTarget and you can check that the naming worked by clicking the dropdown to the right of the Name Box. The name SalesTarget shows up in the list and by selecting it, the active cell jumps to cell K4.

2.    Make your first IF formula

We want messages to appear in column I when the 6-month totals in column H are lower than the target. We will make one in cell I4 that checks Buchanan’s total in H4 and then copy it down.

Select cell I4. Enter the formula below:

=IF(H4<SalesTarget,"Under target","On target")

Copy it to the other cells in column I to check it works for all of the salespeople.

Note that when you enter the first argument of the IF function you can just point and click, like I did, to get the references to cell H4 and to the named cell K4. When you click on K4 Excel inserts the name of the cell SalesTarget into the cell.

What exactly is going on and how does the IF function work in general? In general the IF function has this form:

=IF(Logical_test, Value_if_true, Value_if_false)

The first argument in the parentheses, Logical_test is an expression that evaluates to TRUE or FALSE. In our example, we are comparing the values in two cells, H4 and the SalesTarget cell to see if H4 is less than K4. If it is less, then the IF function will do whatever you put as the second argument, Value_if_true. In our case if H4 < SalesTarget we want it to display the message “Below target”. If H4 isn’t less than SalesTarget then the IF function will do whatever you put as the third argument, Value_if_false. In our case it will display the message “On target”.

3.    Remove the “On target” messages

This works pretty well, but it would be nicer if the “On target” messages didn’t appear at all. After all, they just show that there’s nothing wrong; really we want to just put a warning message when there is something wrong.

So let’s change the prototype formula in cell I4 to suppress those “On target” messages and in the GIFs below we’ll remove the formulae underneath I4 to make it easier to see what we are doing. In the IF function, can we just leave out the third argument? If we do, as the GIF shows, it displays a peculiar message saying FALSE, which is very confusing.

So instead of deleting the third argument all together we will replace it by an ‘empty string’, two double quotes with nothing between them. That then displays nothing when we are on target rather than a superfluous “On target” message.

4.    Make the warnings stand out more

That works pretty well now. But the messages don’t stand out very well because they are just pieces of black writing. Let’s format the font in bold and red. Note that if everyone is on target there will be no red messages.

Excel Masterclasses for aspiring power users

We are launching a range of Masterclasses for Management Consultants and other people who need a higher level of Excel skills than is available from mainstream training companies. The courses are intended for analysts and managers who need to become true Excel power users.

The full range of courses is outlined here.

The first course to be launched is a two-day course on the fundamental subject of Formulae. If you are interested in any of the subjects taught in our more specialised courses such as data visualisation, sensitivity analysis using probability distributions as inputs or what-if analysis, they you should really consider doing the formula course first. For more information on the Formula course or to book take a look here.

The courses will be run using webcasting technology in real-time and with small groups, so there will be plenty of interaction and lots of practical exercises. For the first course we will be using UK timings, starting at 9AM UK local time, but you are welcome to join from outside the UK, and the first course is scheduled for 13-14 June 2019. The course will be taught in English.