The managing director of Blaminio’s construction business was throwing an early fancy dress Christmas bash at his impressive pad last week.

Quite a few of the fittings looked familiar from my patchy knowledge of the company’s stock but I decided not to say anything, at least not for now. No expense was spared on the cocktail sausage and quiche, which is more than can be said for my reindeers outfit complete with red nose.

I bumped into one of our sales guys at the party. The sales guy in question was wearing a blouse (no other word for it) that brought back many New Romantic memories (no I didn’t get it as a Christmas theme either).

It was a little hard to take him seriously as he tried to drink his Becks without ruining his pristine Duran Duran image, pink scarf, mascara and pout. Staring around the red nose, I took my chance to sell him the idea of improved, more detailed numbers enabling him to keep a better track on the spending and profitability of his key clients.

The response I got included several old clichés I’d heard a thousand times before; “naval gazing”, “paralysis by analysis” and “leave me alone I’m trying to enjoy the party”.

But the most cutting remark was “No one in sales reads any of the spreadsheet stuff you send us anyway.” How can they say that when I spend hours working on making reports pretty and intelligible to even the least able of my colleagues?

For instance I am as frustrated as hell by the ugly result in a cell that takes place when a formula is wrong or hasn’t found a result in a set of data elsewhere in the same or another spreadsheet. You know the results I mean – “#DIV/0” or “#N/A”. These ugly returns can ruin the most beautifully crafted report – like somebody painting a moustache on the Mona Lisa. Those error ridden cells look bad and inevitably mean that all other numerical formulae attached to them produce errors in turn.

My favourite solution to the problem is to use the “if(iserror...” function. Using “iserror” allows the user to control what is returned in a cell by a formula if the underlying result is an error.

There are two advantages to this. The first is that a report looks a lot tidier if you can dictate the fact that a zero is returned in a cell rather than something beginning with a #. The second is that any calculation that uses your zero will still work, whereas a #... in a cell will disrupt every calculation that refers to it.

This is particularly useful for my old friend the vlookup function. If you use the vlookup function to return a value from a list, for example, and something has gone awry then you will inevitably end up with a #N/A reference. Wrapping an if(iserror... function around your vlookup effectively allows you to say “if my answer’s rubbish then give me something in the cell that I can make sense of, or will at least add up, if everything’s fine then carry on as usual”. For example; =if(iserror(insert your vlookup function),”0”,(insert your vlookup function)).

You don’t have to return a zero if there’s a problem – you can enter something as simple as “check”, which still looks a little tidier than a report full of hashes. You just need to remember that returning a zero will not disrupt any of the other calculations in the spreadsheet. If you do decide to return a zero, excel still allows you to identify that there’s a problem with the formula by showing the zero on the left hand side of the cell.

Despite my careful and detailed explanation– only slightly hampered by the loud rendition of Slade’s Merry Christmas – I’m not convinced he was listening properly. Even so I remain convinced the result is so simple that even a salesmen dressed as Simon Le Bon would get it, wouldn’t he? 

*Barry Cells was talking to Peter Charles who does not necessarily agree with any of his views on Christmas fancy dress parties, Simon le Bon, Duran Duran or sales people.

peter@petercharles.com; www.petercharles.com.

Related tags: duran duran, iserror, excel, spreadsheet, vlookup function, barry cells,