Doing Calculations in Tables, in OpenOffice Writer

Doing calculations in tables is a nice feature of OpenOffice.org Writer. You can essentially treat a table like a spreadsheet. With this example, for instance, I can just use spreadsheet-like calculations in the table to get the totals and differences.

The process is a bit different in 2.0 so I'm blogging it here; plus anyone who hasn't used the feature before can see if they like it.

Note: I personally prefer to use spreadsheets, so I would do the calculations in a spreadsheet and then just copy and paste. But for those who like tables, here's how you do it.

This process focuses on summing, with some other options.

First, of course, create the table the way you want it.

Then click in the empty cell where you want a calculation. Choose Table > Formula or press F2 and you'll get the spreadsheet-like toolbar with the formula entry field.

If you know what you want and just want to go right ahead and do it, just type what you want using <> around each cell reference. <F5>, <A1>, etc.

If you want some help from the system, do it this way. Select the cells you want to calculate; you'll get an addition formula by default. This will sum the contents of the selected cell range.

If it's what you want, press Enter or click the green arrow by the formula field. The total will appear.

If it isn't what you want, just retype the formula in the formula field. Be sure to preserve the syntax with the <> around each cell reference. Cell references are the same as Calc, with A1 being the upper left cell of a table and counting across to B and down to 2, and so on. A correct subtraction formula for instance is =<b2>-<c2>

In the following example, I of course want to find the difference, not the sum, of the expenses and income, so I changed the formula.

If you want something more complicated than addition, subtraction, multiplication, and division, click on the formula dropdown list.

To format the cells automatically with dollar signs, etc.. select the cells, right click, and choose Number Format. In the window that appears, select the format you want. Click the following image to see a larger image of the window, if you want.

 

Overall, the table formulas work but they're just a bit twitchy. If you've got something beyond simple math, I suggest doing it in a spreadsheet, then copying and pasting the spreadsheet into Writer.

---

---

Tags: OpenOffice.org  openoffice  OpenOffice.org training  openoffice training  open source  open source training  StarOffice  Linux   tagtest   education  Google  Sun 

Comments

[Valerio F. Laube]

If my table have some cells merged, will calculation work ? How can I identify lines and columns ?
Thanks

[Alex Lush]

Anyone know how to prevent zero values from displaying in Writer using tables? I have a table working out totals from quantity and unit price columns, but only want the formula to display if the quantity is greater than 0. Any ideas if this is possible in Writer, or will I have to use Calc?

[Sanguis]

Yes but is there a way to calculate the value for en entire column?

[Solveig]

Hi Sanguis,

You just have to put in the range, like A1 to A56.

Solveig

[Charlotte]

After pressing enter and seeing the result of the formula in numbers, how do you see again the formula instead of the numbers?

[Solveig]

Hi Charlotte,

Move your mouse over the cell and a tooltip will pop up.

It's realllllly cumbersome so I strongly recommend doing it in a spreadsheet and pasting it in or choosing Insert > Object > OLE Object, then select Create new and Spreadheet.

[Charlotte]

Thanks for your answer. Now that I see how the OLE works insert, I think it is better. It also seems to convert to .doc in case the document has to be sent to someone with Word, whereas the formulas in a table appear to become "hard-wired" as actual numbers if saved-as .doc.

[tiffany bracelet,tiffany bangle]

GOOD