Chapter 16 · 16.19
Calculating in a table in a text document (quote or invoice)
Freelancers and small businesses often create quotes and invoices in the word processor: a table with description, quantity, price and amount, with the total and VAT at the bottom. Euro-Office can calculate in a table like this using formulas, just like Word. However, there is an important pitfall for amounts written with a decimal comma, which we ran into in the demo. So read this whole article before you send a quote.
As an example, we use a quote from Bloemisterij Het Zonnetje for a wedding.
Inserting a formula
- Click in the cell where the result should appear, for example the amount next to "Corsages".
- Click the table settings icon on the right and click Add formula at the bottom.
- The Formula settings window opens. Euro-Office suggests a formula itself, such as =SUM(LEFT) (add up everything to the left) or =SUM(ABOVE) (add up everything above).
- Type your own formula, for example =B3*C3: quantity times price. The columns are called A, B, C, D from left to right, and the rows 1, 2, 3 from top to bottom, including the header row.
- Under Number format, choose how the number should look, and click OK.

The functions have the same English names as in Word: SUM adds up values. You can choose other functions from the Paste function list. LEFT and ABOVE stand for all cells to the left of or above the cell.
The pitfall: the comma
Formulas in a text document do not read a comma as a decimal separator. In the demo, 12 corsages times 8,50 (typed with a decimal comma) gave the result 696 instead of 102. A number such as 0,09 in a formula also gives an error message (!Syntax error). And because the standard formats use a comma as the thousands separator (1,216.00), any further formula that uses that amount also calculates incorrectly.

How to make the table calculate correctly
- Type the prices in the table with a point as the decimal separator: 8.50 instead of 8,50.
- Under Number format, choose the format 0.00, without a thousands separator.
- Use a point in the formula as well: =D6*0.09 for 9% VAT.
In the demo, this gave exactly the right results: 12 x 8.50 = 102.00, subtotal 622.00, VAT 55.98 and total including VAT 677.98.

Note: the amounts always appear with a decimal point (102.00). If your customers expect a decimal comma, as in many European countries (102,00), that is fine for an internal calculation but often not for a quote to a customer.
Updating
A formula does not recalculate automatically when you change a quantity or price. Select everything with Ctrl + A and press F9 to update all formulas. Always do this just before you send the quote as a PDF (see Printing and saving as PDF).
Better: calculating in a spreadsheet
For quotes and invoices with euros and VAT, a spreadsheet is more reliable. There, everything recalculates immediately, amounts with a decimal comma simply work, and amounts appear in the notation of your region (for example €1,216.00). Do the calculation in a spreadsheet (see Formulas and functions: SUM, AVERAGE, IF and VLOOKUP) and copy the table into your quote, or send the quote as a PDF from the spreadsheet.
Pitfalls
- An empty cell or text such as "n/a" in a column you are adding up can disrupt the result. Enter 0 there or leave the cell completely empty.
- If you insert a row, the cell names shift (B3 becomes B4). Formulas such as =SUM(ABOVE) are not affected; check formulas with fixed cells again.
- Do not type a euro sign in the number format. Put the euro sign in the header row ("Amount in €").
- If you want to round VAT per line or over the total, that is also easier in a spreadsheet: see Rounding VAT correctly: per line or over the total.
