Amounts in words in Excel and Google Sheets

VBA, in Excel. Microsoft's guide, Create custom functions in Excel, gives the steps: “Press Alt+F11 to open the Visual Basic Editor (on the Mac, press FN+ALT+F11), and then click Insert > Module.” Paste the module into the new window. The function then appears, in Microsoft's words, “in the User Defined category” of the Insert Function dialog box.

Apps Script, in Google Sheets. Google's guide, Custom functions in Google Sheets: “Select the menu item Extensions > Apps Script. Delete any code in the script editor.” Paste the function, and save. The two helper functions end in an underscore, which Google says “denotes a private function in Apps Script”, so only AMOUNTINWORDS is offered in the sheet.

All three are generated on this page from the word lists the rest of the site uses, and each is tested against the money converter before any change is published. The tests run them on 40 amounts chosen by hand, where number words usually go wrong (0.01, 1,000,005, 999,999,999,999.99, negatives and more), in all 312 combinations of currency, English, style, cents and letters that this page offers, and on 20,000 more amounts drawn at random below a trillion and shared out among those combinations. Every answer has to match the converter's word for word.

Rounding to the cent is tested on its own sets: every half cent from 0.005 to 99.995, 10,000 of them, and each of them below zero; 5,000 half cents from one thousand to 999,999,999,999.995, most drawn at random; 5,000 amounts with three or more decimal places, half of them a hair from a half cent; and 5,000 totals worked out in binary from a price and a rate such as 15%, the way a spreadsheet works them out. Each has to come to the cent it comes to on paper, with half a cent rounded away from zero.

The Apps Script is JavaScript, so the tests run the generated code itself.

The Excel formula is run through a small evaluator written for this site. It implements only the eighteen functions the formula can call, ABS, AND, IF, IFERROR, INT, ISNUMBER, LEFT, LEN, LET, LOG10, MID, MOD, PROPER, RIGHT, ROUND, SUBSTITUTE, TRIM and UPPER, each as Microsoft's documentation describes it, and it stops with an error on any function it does not know. The points that catch out a copy of Excel's rules written from memory are modelled as documented: TRIM “removes all spaces from text except for single spaces between words”, and MOD(n, d) = n - d*INT(n/d). Every amount is run twice, once with ROUND working on the binary number and once with it working on the number to fifteen significant digits, and both give the same answers. The formula has not been run in Excel itself.

The VBA module is translated into JavaScript, statement by statement, by a translator that accepts only the statements the module uses and stops on any other, and the translation is run on the same amounts. Each is given as a Double and as a cell formatted as currency, and each amount written as text also as a Decimal and, up to four decimal places, as Currency; CDec is run turning a Double into a Decimal both as the VBA language specification describes and to fifteen significant digits. It has not been run in Excel either.

So the tests show that the logic of each version gives the converter's answer. They cannot show how a particular copy of Excel reads the text you paste. If one of them ever gives a different answer from the money converter, the converter is the one to trust, and the contact page says how to tell us.

• Up to 999,999,999,999.99. Microsoft gives Excel's number precision as 15 digits; twelve before the point and two after fit inside it. From one trillion up, all three return “Too large to write out”.
• Rounded to the cent. An amount with more decimal places, such as a total worked out by another formula, is taken to fifteen significant digits and rounded to the nearest cent, with half a cent rounded away from zero: 1.005 gives “one rand and one cent” and -1.005 gives “minus one rand and one cent”. A spreadsheet holds 1.005 as a binary number a shade under it, and a price times 15% can land a shade either side of a half cent; at fifteen digits each is the half cent again. To match the amount on an invoice, round the total in its own cell.
• Blank cells, text, TRUE and FALSE. A blank cell gives a blank in all three. Text, including a number stored as text, and TRUE or FALSE give “Not a number” in all three.
• Dates and errors. Here the three differ. Microsoft's DATE function page says “Excel stores dates as sequential serial numbers so that they can be used in calculations”, and the formula writes that number: 1 January 2008, serial number 39448, gives “thirty-nine thousand four hundred and forty-eight rand”. The VBA module answers “Not a number”, since VBA's IsNumeric “returns False if expression is a date expression”, and so does the Apps Script, since in Google's words “Times and dates in Sheets become Date objects in Apps Script.” An error value such as #N/A gives “Not a number” from the formula and the VBA module. Google's guide to custom functions does not say what one receives from a cell holding an error, and that was not tested.
• The formula's size. With the settings above as they load, it is 2,351 characters long, nests functions 8 deep and names 28 values in its LET; the longest form the page can write, with every option that adds to it and a cell reference as long as Excel allows, is 2,481 characters, and none nests deeper than 8. Microsoft's Excel specifications and limits allow 8,192 characters of formula contents and 64 nested levels of functions, and the LET function supports up to 126 names. The tests hold every form of the formula to all three.
• English function names. The formula uses Excel's English function names. Choose semicolons above if your copy of Excel separates a function's arguments with semicolons; the formula holds no decimal number, so the decimal separator never matters.