Open Source Team

  • Increase font size
  • Default font size
  • Decrease font size

OpenOffice Writer: Using formulas in tables

E-mail Print

The advantage of using formulas in tables

Most people will know how to use tables in OpenOffice Writer. Sometimes however, you want to do calculations in your table, such as sums, substractions or more complex arithmetic. One way to do this, would be to do those calculations in an OpenOffice Calc spreadsheet, and to copy and paste the contents in a table in your document. While this may be a 'good enough' solution in same cases, it has some disadvantages. You have to open two different applications. In addition, a copy and paste operation is a manual operation. This means that there is always something that can go wrong. If you are not careful, you may paste your content in the wrong place. Suppose you have to send an invoice on a monthly basis, or your management expect you present a weekly report showing the status of your project. If you each time have to resort to copy and pasting in those situations, it may not be such a good solution. Using formulas in tables then provides a better solution.

How it works

To explain how this works, lets look at the example of management student John, who is helping a hand in a child day care center. The first thing that strikes John, is the utter lack of production reporting in this day care center. Management has no clue of the daily number of drawings produced there! That's why John prepares a brief report, using OpenOffice Writer, which he presents to the center's management. The report is well received among the day care management. It looks like:

status report 1

Pleased with the new insights this report offered, the management encourages our friend John to produce this report on a weekly basis. This leads him to look for a way to automate the calculations in this report. He realizes that he can do this, using formulas in tables! His will build a template that he can use every week. Firstly, he empties the calculated cells. Since the daily cookie consumption and the daily drawing speed are stable characteristic for a given toddler, the fields are not emptied. This results in the following situation:

status report preparation for formulas

In the cell for total number of days present, he clicks F2, or alternatively, he could select -> Table -> Formula.

formula

A calculation fields appears in which he enters the formula to be shown in the selected cell. The formula here is: =sum(B2:B5) Note that for other functions than sum, such as, there is a dropdown list where other functions can be selected to the right of the formula symbol. As soon as he clicks 'Enter' the result appears in the selected cells. In the following cells, using the same approach, he fills in the respected formulas. Or more specifically, in the first row (Billy's row): cookies eaten this week: <B2>*<C2> and for drawings made this week: <B2>*<E2>. Finally ,the sums in the last row, are calculated using the sum(first cell in colum: last cell in column).

The finishing touch: protecting cells

John realizes that the only cells he has to fill when he creates a new report, are the days of presence of the four toddlers. To avoid changing cells by mistake, it is a good idea to protect the other cells. To do this, he selects the entire table, and select -> Table -> Protect Cells.

The mentioned four cells should not be protected: that is where the data has to be entered. To unprotect them, unfortunately you can not do this in one go. You have to select one by one and right click on the cell -> Cell -> Unprotect.

Note 1: protected cells are not password protected. They are good a good technique to protect against unintented changes, but do not offer protection against malicious modifications.