Six baffling Excel functions

Originally published on my company internal blog.

BAHTTEXT

What it does: The baht is the currency of Thailand. BAHTTEXT(123) returns the words “One hundred and twenty-three baht”, but in Thai.

Why it’s baffling: Because this is the only function in Excel that does anything like this, and it’s only available for the baht. There’s no equivalent for converting a number to text in any other currency or language.

CODE

What it does: CODE(“HELLO”) returns the code for the letter “H” in the computer’s current character set

Why it’s baffling: For a start, it’s somewhat difficult to see why anyone might ever want to use Excel to get the ASCII / ANSI / whatever code for the first letter of a string. And then there’s the contortions you’d have to go through if you wanted, say, the ASCII code for the second letter instead.

DAYS360

What it does: Returns the interval between two dates, in days, whilst assuming that all years have 360 days in them.

Why it’s baffling: I can just about see why a company might have equal-length accounting periods – 12 months times 30 days – but I’m seriously struggling to see why you’d want to do date arithmetic on this basis. An optional second argument gives you a modicum of control over which five or six days of the year get ignored but still leaves open the awful question: what if my birthday no longer exists? Do I still get cake? Also, there are now automatically five (or six) fewer shopping days left until Christmas… I think. And there now exists both a 29th and a 30th of February every year.

ROMAN

What it does: Converts from numbers to a text string in Roman numerals. For example, ROMAN(5) returns “V” and ROMAN(21) returns “XXI”.

Why it’s baffling: Three reasons. First, unless you need to put a copyright date at the end of a TV programme, why would you ever want Excel to work in Roman numerals? Second, there’s a bewildering array of optional arguments that return strings in different forms, most of which, I suspect, wouldn’t mean much to a real Roman. Third, there’s no function for converting a Roman numeral string back into conventional Arabic digits – so having converted a number to Roman, it can’t be processed any further.

SUBTOTAL

What it does: Sums across a range of cells but excludes any cells that already contain a SUBTOTAL function. This means that you can use a SUBTOTAL function on a group of lines, and a SUBTOTAL again against a group of groups, without counting the same figures twice.

Why it’s baffling: Because it doesn’t actually sum, and hence doesn’t actually provide a subtotal, unless you give it a very specific first argument: the number 9. Not only do you have to know that the first argument is one of 22 pre-defined function reference numbers, but you also have to remember that the most obvious default number is not 1, but 9.

DAVERAGE

What it does: There’s a range of database functions whose names start with a “D”. This one returns the average from a database or list of values.

Why it’s baffling: OK, actually, it’s quite straightforward and not baffling at all. But I like to think of this function as being called DAVE_RAGE which nicely defines my normal experience when using Excel.

Published
Categorised as Excel, Humour

By David A

Imagination of a poet - communication skills of an engineer. In no particular order, I like old cinemas, Japanese gardens, playing music, giraffes, technology, films, ducks, narrow Italian streets, and trying to write stuff that's interesting to people. I've been blogging irregularly since 2005 and have had some form of website since 1998.