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:
Click on the Format toggle below the text area.
Type "$" in first text field.
Type "#,##0.00" in second text field.
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.
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:
Double-click on the formula of cell E7 and replace =rounddown((E6 * (left(D7, (len(D7) - 1)))%), 2) by =rounddown((numbervalue(E6, "$#,##0.00") * (left(D7, (len(D7) - 1)))%), 2). Also use the Format fields.
Double-click on the formula of cell E8 and replace =(E6 + E7) by =(numbervalue(E6, "$#,##0.00") + numbervalue(E7, "$#,##0.00")). Also use the Format fields.
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 using
| | .