Doing Calculations in Tables, in OpenOffice Writer (Repost)

(First posted February 2006)

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

[John Williams]

Dear Solveig,

Thanks for this tip - I'm finding it useful just at this very moment. However, like you, "I personally prefer to use spreadsheets, so I would do the calculations in a spreadsheet and then just copy and paste". Yet when I try to do this from Calc to Write, it just pastes the cells as an image rather than a table. I can stretch and skew the image but can't manipulate the table as such. Any ideas? I hope this isn't too dumb a question.

I'm using OpenOffice under Windows XP.

Thanks,

John

[Solveig]

Hi John,

There are no dumb questions! Good point. To manipulate the pasted spreadsheet in a Writer doc, click and/or double-click the spreadsheet. (I find it varies, either that or my memory is slipping. ;> ) Then the toolbars change and you get the Calc toolbars. Select the cells or click in them to do what you want, then click back in the main part of the Writer doc to get out again.

[George Brower]

And how do you copy a formula so that if cell g5 contains =* cell g6 contains =*?

[George Brower]

And how do you post a comment that contains a formula so that the formula is visible to forum readers? In the most recent case:
"And how do you copy a formula so that if cell g5 contains =* cell g6 contains =*?"
has no meaning. There should have been an "" before the first "*" and an "" after the first "*"
The text before and after the second "*" should have been the same, except that the cell addresses should have referred to row 6.

[George Brower]

Having copied my data from writer to calc, done the calculation described in my previous post for each row, how do I paste the results into the writer table so that each result goes in its own row / cell?

I've tried Edit>Paste Special>Unformatted Text and Edit>Paste Special>Formatted Text [RTF], both of which put all the results in one cell. Choosing the column that should contain the result rather than its first cell made no difference.

Word processor tables are not spreadsheets, but it seems reasonable to expect them to do the basics (copy and paste with cell address adjustments, paste a group of cells into a same-size group of cells (whether the cells come from a word processor table or a spreadsheet), in a congruent way.

[Solveig]

Hi George,
If you select cells in Calc and then go to Writer, use paste special with Formatted text which should put each Calc cell in a separate Writer table cell. Just do a paste into an empty line of the document and it should create its own table successfully.

Solveig