6. Formatting

There is something obviously wrong in our invoice: numbers are very poorly formatted. Replace the unit prices of column D by $200.00, $800.00, $3,000.00, $100.00.

The problem now is that the formulas no longer work[2]. The reason is that, if string "3000" can be automatically be converted to a number when used in formula =A4*D4, a string such as "$3,000.00" cannot be automatically be converted to a number.

In order to fix this, we need to use spreadsheet function numbervalue(). This function must be used to convert a string representing a localized number to something usable by the spreadsheet functions and operators. Function numbervalue() must be passed a number format and optionally, a locale which specifies how to interpret this format.

First of all, set the xml:lang attribute of the html root element to en. By doing this, all the number formats found in spreadsheet formulas will by default use the English locale.

Then, click on the formula of cell E2 and replace =(A2 * D2) by =(A2 * numbervalue(D2, "$#,##0.00")).

While we are at it, we'll also make the value computed by the formula of cell E2 good looking. In order to do that:

We could have used spreadsheet function text() to accomplish the same formatting task, but separating the calculation from the formatting of the result by the means of the Format fields will make your formulas easier to read.

Do not bother fixing by hand the formulas of cells E3, E4, E5. Simply copy the formula found in cell E2 (Ctrl+C) then click to select the formula of cell E3 and finally use paste (Ctrl+V) to replace it by the content of the clipboard. Repeat the operation with cell E4 and cell E5.

The formula of cell E6 is trickier to fix: =sum(difference(E:E, E6:E1000)). In order to do this, we need to use spreadsheet function apply().

Function apply() applies a transformation to each node of a nodeset. Here the nodeset is: difference(E:E, E6:E1000). The transformation that we need to apply is numbervalue(x, "$#,##0.00"), where x represents the string value of the iterated node. This gives:

Note that the transform argument of apply is specified as a string and that we have alternated single and double quotes to make this string easier to read.

[Tip]Tip

The documentation of spreadsheet functions is available online. If, for example, you don't remember how to use function apply(), simply select the word apply in the text area of the Formula Editor and press F1.

Finally fix the formulas found in cells E7 and E8:

The invoice is finished. It is now possible to remove the labels we put around the rows and columns of the table. This is done by selecting ToolsSpreadsheetShow Table Labels one more time.



[2] NaN is a special number which means "Not a Number".

[3] Example: numbervalue(D2, "$#,##0.00", "en-US").