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.