3. Relative references

Select row #4 (for example by Ctrl-clicking 3 times in a cell of row #4), copy it to the clipboard (Ctrl+C) and paste the copied row after row #4 (Ctrl+W).

Ouch! SubTotal E6 is wrong. It should be 7200. What happened here?

The first formula we have created was =A2*D2. This formula was inserted in cell E2. This formula uses relative cell references which means that XXE understands it as: add the cell which is 4 columns to my left to the cell which is 1 column to my left.

If you really wanted to add cell at (row 2,column A) to cell at (row 2,column D), whichever is the cell containing the formula, you should have typed =$A$2*$D$2.

Using relative references in a formula is handy because it allows the formula to be copied and pasted elsewhere. Remember that we have copied first formula to E3 and E4 and that we have duplicated row #4, which means that we have copied the formula of E4 to E5.

If you click on the formula of E3 (which is a copy of the one in E2), you'll see =(A3 * D3). Similarly, on E4, you'll see =(A4 * D4) and on E5, =(E5 * D5).

No, XXE has not modified the formulas that you have copied. XXE has stored exactly the same formula in E2, E3, E4, E5 but it displays it differently when you click on different cells.

And because, unlike ``real spreadsheet software'', XXE never modifies your formulas, SubTotal E6 is wrong! Click on E6 and you'll see =sum(E3:E5). Real spreadsheet software would have modified the formula to be =sum(E2:E5).

Fortunately, there is a way to fix this kind of problem. Double-click on the formula of E6 to open the Formula Editor and replace =sum(E3:E5) by =sum(difference(E:E,E6:E1000)). This means add everything in column E (E:E) except all cells after E6 (E6:E1000). Et voilà this is fixed once for all! You can now freely add and remove purchased products to the table without worrying about the SubTotal.

Duplicate row #5 as we did for row #4. In new row #6, replace 1,fc-uu,3000 by 1,sc-cs,100. Then delete row #5 which is a copy of row #4.