Creating formulas

From Apache OpenOffice Wiki
< Documentation‎ | OOo3 User Guides‎ | Calc Guide
Revision as of 03:44, 29 September 2009 by Kirk (Talk | contribs)

Jump to: navigation, search


By convention, only three kinds of data can be entered in a cell. Text, Numbers, and Formulas. You can enter formulas in two ways, either in the cell itself, or at the input line. Either way you will need to start it with one of the following symbols: "=", "+" or "-". Starting with anything else will cause the formula to be treated as if it were text.

Operators in formulas

Each cell on the worksheet can be used as a data holder or a place for data calculations. Entering data is accomplished simply by typing in the cell and moving to the next cell or pressing Enter. With formulas the equals sign indicates that the cell will be used for a calculation. A mathematical calculation like 15 + 46 can be accomplished as shown below.


Simple Calculation in 1 Cell Calculation by Reference
CG3Ch3F5a.png CG3Ch3F5b.png
CG3Ch3F5c.png CG3Ch3F5e.png

A simple calculation.

While the calculation on the left was accomplished in only one cell, the real power is shown on the right where the data is placed in cells and the calculation is performed using references back to the cells. In this case, cells B3 and B4 were the data holders with B5 the cell where the calculation was performed. Note that the formula was shown as =B3 + B4. The plus sign indicates that the contents of cells B3 and B4 are to be added together and then have the result in the cell holding the formula. All formulas build upon this concept. Other ways of entering formulas are shown in Table 1.

These cell references allow formulas to use data from anywhere in the worksheet being worked on or from any other worksheet in the workbook that is opened. If the data needed was on different worksheets they would be referenced by referring to the worksheet, for example =SUM(Sheet2.B12+Sheet3.A11).

Table 1: Common ways to enter formulas.

Formula Description
=A1+10 Displays the contents of cell A1 plus 10.
=A1*16% Displays 16% of the contents of A1.
=A1 * A2 Displays the result of the multiplication of A1 and A2.
=ROUND(A1;1) Displays the contents of cell A1 rounded to one decimal place.
=EFFECTIVE(5%;12) Calculates the effective interest for 5% annual nominal interest with 12 payments a year.
=B8-SUM(B10:B14) Calculates B8 minus the sum of the cells B10 to B14.
=SUM(B8;SUM(B10:B14)) Calculates the sum of cells B10 to B14 and adds the value to B8.
=SUM(B1:B65536) Sums all numbers in column B.
=AVERAGE(BloodSugar) Displays the average of a named range defined under the name BloodSugar.
=IF(C31>140; "HIGH"; "OK") Displays the results of a conditional analysis of data from two sources. If C31 = 144, then HIGH is displayed, otherwise OK is displayed.


Functions can be identified in Table 1 with a word, for example ROUND, followed by parentheses enclosing references or numbers.

It is also possible to establish ranges for inclusion by naming them using Insert > Names, for example BloodSugar representing a range such as B3:B10. Logical functions can also be performed as represented by the IF statement which results in a conditional response based upon the data in the identified cell.

Operator types

You can use the following operators in Calc: arithmetic, comparative, text, and reference.

Arithmetic operators

The addition, subtraction, multiplication and division operators return numerical results. The Negation and Percent operators identify a characteristic of the number found in the cell, for example -37. The example for Exponentiation illustrates how to enter a number that is being multiplied by itself a certain number of times, for example 23 = 2*2*2.

Table 2: Arithmetical operators

Operator Name Example
+ (Plus) Addition =1+1
- (Minus) Subtraction =2-1
- (Minus) Negation -5
* (asterisk) Multiplication =2*2
/ (Slash) Division =10/5
 % (Percent) Percent 15%
^ (Caret) Exponentiation 2^3

Comparative operators

Comparative operators are found in formulas that use the IF function and return either a true or false answer; for example, =IF(B6>G12; 127; 0) which, loosely translated, means if the contents of cell B6 are greater than the contents of cell G12, then return the number 127, otherwise return the number 0.

A direct answer of TRUE or FALSE can be obtained by entering a formula such as =B6>B12. If the numbers found in the referenced cells are accurately represented, the answer TRUE is returned, otherwise FALSE is returned.

Table 3: Comparative operators

Operator Name Example
= (equal sign) Equal A1=B1
> (Greater than) Greater than A1>B1
< (Less than) Less than A1<B1
>= (Greater than or equal to) Greater than or equal to A1>=B1
<= (Less than or equal to) Less than or equal to A1<=B1
<> (Inequality) Inequality A1<>B1

Text operators

It is common for users to place text in spreadsheets. To provide for variability in what and how this type of data is displayed, text can be joined together in pieces coming from different places on the spreadsheet. Below is an example.



Text Concatenation.

In this example, specific pieces of the text were found in three different cells. To join these segments together, the formula also adds required spaces and punctuation housed within quotation marks resulting in a formula of =B6 & " " & C6 & ", " D6. The result is the concatenation into a correctly formatted date for this locale.

Taking this example further, the result cell is defined as a name, then text concatenation is performed using this defined name.

Defining a name for a range of cells.
Naming a cell or range of cells for inclusion in a formula.




Defining Names on a worksheet.

Reference operators

In its simplest form a reference refers to a single cell. But references can also refer to a rectangle or cuboid range or a reference is a list of references. To build such references you need reference operators.

An individual cell is identified by the column identifier (characters) located along the upper edge of the spreadsheet and a row identifier (number) found along the side of the spreadsheet. On spreadsheets read from left to right, the upper left cell is A1. The figure below shows these identifiers for Cell A1.

A single cell reference

Range operator

The range operator is written as colon. An expression using the range operator has the syntax

reference left : reference right

The range operator builds a reference to the smallest range including both the cells referenced with the left reference and the cells referenced with the right reference.

Reference to a rectangle range.

The picture should be exchanged with one using the new transparent highlighting for selections. Regina 18:33, 19 September 2009 (UTC)

In the upper left corner of the figure above, the reference A1:D12 is shown, corresponding to the cells included in the drag operation with the mouse to highlight the range.

The following is about selecting and naming cells and should be moved somewhere else or be dropped.Regina 18:33, 19 September 2009 (UTC)

[start to move or drop] This same range could also be created <--wrong term! by entering in the Name Box directly as shown below. After pressing Enter, the same range is selected.

Direct entry of Reference Operator into Name Box.

A reference operator <--wrong term! can also be created by defining a named area by selecting the menu item Insert > Names > Define, pressing Ctrl+F3, or clicking the icon, if it shows on your toolbar.

[end to move or drop]


A2:B4 Reference to a rectangle range with 6 cells, 2 column width × 3 row height. When you click on the reference in the formula in the input line, a border indicates the rectangle.
(A2:B4):C9 Reference to a rectangle range with cell A2 top left and cell C9 bottom right. So the range contains 24 cells, 3 column width × 8 row height.
Sheet1.A3:Sheet3.D4 Reference to a cuboid range with 24 cells, 4 column width × 2 row height × 3 sheets depth.

When you enter B4:A2 or A4:B2 directly, then OOo will turn it to A2:B4. So the left top cell of the range is left of the colon and the bottom right cell is right of the colon. But if you name the cell B4 for example with '_start' and A2 with '_end', you can use _start:_end without any error.

OOo can not reference a whole column of unspecified length via A:A or a whole row via 1:1 yet as you might know from other spreadsheet programs, see Issue 20495 .

Reference Concatenation Operator

The concatenation operator is written as tilde. An expression using the concatenation operator has the syntax

reference left ~ reference right

The result of such an expression is a reference list, which is an ordered list of references. Some functions can take a reference list as argument, SUM, MAX or INDEX for example.

The reference concatenation is sometimes called 'union'. But it is no set union. COUNT(A1:C3~B2:D2) returns 12 (=9+3), whereas looking at it as set of cells, it has only 10 cells.

Notice that SUM(A1:C3;B2:D2) is different from SUM( A1:C3~B2:D2) although they give the same result. The first is a function call with 2 parameters, each of them is reference to a range. The second is a function call with 1 parameter, which is a reference list.

Intersection Operator

The intersection operator is written as exclamation mark. An expression using the intersection operator has the syntax

reference left ! reference right

If the references refer to single ranges, the result is a reference to a single range, containing all cells, which are both in the left reference and in the right reference.

If the references are reference lists, than each list item from the left is intersected with each one from the right and these results are concatenated to a reference list. The order is, to first intersect the first item from the left with all items from the right, then intersect the second item from the left with all items from the right, and so on.


A2:B4 ! B3:D6 This results a reference to the range B3:B4, because these cells are inside A2:B4 and inside B3:D4.
(A2:B4~B1:C2) ! (B2:C6~C1:D3) First the intersections A2:B4!B2:C6, A2:B4!C1:D3, B1:C2!B2:C6 and B1:C2!C1:D3 are calculated. This results in B2:B4, empty, B2:C2, and C1:C2. Then these results are concatenated, dropping empty parts. So the final result is the reference list B2:B4 ~ B2:C2 ~ C1:C2.

You can use the intersection operator to refer a cell in a cross tabulation in an good understandable way. If you have columns labeled 'Temperature' and 'Precipitation' and the rows labeled 'January', 'February', 'March',… then the expression

'February' ! !Temperature'

will reference to the cell containing the temperature in February.

The intersection operator ! should have a higher precedence than the concatenation operator ~, but in OOo3.1 it is wrong implemented. Therefore you should not rely on precedence, but always put the part which to be first calculated in parentheses.

Order of calculation

Order of calculation refers to the sequence that numerical operations are performed. Division and multiplication are performed before addition or subtraction. There is a common tendency to expect calculations to be made from left to right as the equation would be read in English. Calc evaluates the entire formula, then based upon programming precedence breaks the formula down executing multiplication and division operations before other operations. Due to this fact when creating formulas you should test your formula to make sure that the correct result is being obtained. An example of order of calculation in operation follows.

Table 4 – Order of Calculation

Left To Right Calculation Ordered Calculation
1+3*2+3 = 11 =1+3*2+3 result 10
1+3=4, then 4 X 2 = 8, then 8 + 3 = 11 3*2=6, then 1 + 6 + 3 = 10
Another possible intention could be: The program resolves the multiplication of 3 X 2 before dealing with the numbers being added.
1+3*2+3 = 20

If you intend for the result to be either of the two possible solutions on the left, the way to achieve these results would be to order the formula as:

((1+3) * 2)+3 = 11 (1+3) * (2+3) = 20


Calculations linking sheets

Another powerful feature of Calc is the ability to link data through several worksheets. The naming of worksheets can be helpful to identify where specific data may be found. A name such a Payroll or Boise Sales is much more meaningful than Sheet1. The function named SHEET() returns the sheet number in the collection of spreadsheets. With this function, if you are currently on the third sheet counting from the left along the worksheet tabs, the number 3 is returned. If you drag the worksheets around to different locations among the tabs, the number will return the number referring to the current position of this worksheet.

Documentation caution.png Users may experience a problem when creating a formula that references other worksheets. If you use the function SHEET() the number of the current worksheet appears even if you enter the name of the worksheet desired, e.g., =SHEET(Branch4) and you have as the top worksheet named CombinedOperations. This function only refers to the current or top worksheet and not to other worksheets found in the collection or file.

An example of calculations obtaining data from other work can be seen in a business setting where a business combines its branch operations into a single worksheet.

CG3Ch3F14a.png Sheet containing data for Branch 1.
CG3Ch3F14b.png Sheet containing data for Branch 2.
CG3Ch3F14c.png Sheet containing data for Branch 3.
CG3Ch3F14d.png Sheet containing combined data for all branches.

Combining data from several sheets into a single sheet.

To accomplish the links, it can be easier if the Function Wizard is used.

  1. Open the Function Wizard by clicking the f(x) button on the toolbar, or by selecting Insert > Function, or by pressing Ctrl+F2.
  2. Select a category of functions to shorten the list, then scroll down through the named functions and select the required one. When you select a function, its description appears on the right-hand side of the dialog. Double-click on the required function.
  3. The wizard now displays an area to the right where you can enter data manually in text boxes or click the Shrink button [[Image:]] to shrink the wizard so you can select the cells from the worksheet.
  4. To select cells, either click directly upon the needed cell or hold down the left mouse button and drag to select the needed area.
  5. When the area has been selected, click the Shrink button again to return to the wizard.
  6. If multiple ranges are needed, then select the next text box below the first and perform the same task for the next range. The wizard will accept in the Sum function up to 30 such ranges.

The following figures show how to create the combined sheet shown in the example above.

Selecting the SUM function in the Function Wizard.
Further steps in selecting cells containing data.
Completed entries in Function Wizard.

Strategies for creating formulas

There are several broad approaches when making a decision to create a formula. In deciding on an approach, consider how many other people will need to use the worksheets, the life of the worksheets, and the variations that could be encountered from use.

Enter a unique formula in each cell

The first and most basic strategy is to view whatever formulas are needed as simple and with a limited use life. The strategy is then to place a unique formula in each appropriate cell.

Break formulas into parts and combine the parts

The second strategy is similar, but instead you break down longer formulas into smaller parts and then combine the parts into the whole.

Editor's note: This needs some explanation, but it is NOT about the "formula editor" (Math).

Use the Basic editor to create functions

A third strategy is to use the Basic editor and create your own functions. This approach would be used where the result would greatly simplify the use of the spreadsheet by the end user and keep the formulas simple with a better chance of avoiding errors. This approach also can make the maintenance easier by having corrections or updates kept in one central location. The use of macros is described in Chapter 12 of this book.

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools