Formula Elements

Formulas are the key to making a powerful spreadsheet. A formula instructs Gnumeric to perform calculations and display the results. These calculations are defined as a formula data elements. The power of these formulas arises because these formulas can include the contents of other cells and the results of the formulas are updated automatically when the contents of any cell included in the formula change. The contents of other cells are included using "cell references" which are explained below.

Any formula entered into a cell must follow a specific syntax so that Gnumeric can interpret the formula correctly. This syntax closely follows mathematical notation but also includes spreadsheet formulas, object names and cell references.

5.2.4.1. Syntax

Formulas are distinguished from regular data by starting with an equals sign (=) as the first character. Everything following this equals sign is evaluated as a formula.

Alternate Beginnings for Formulas

To accommodate those more familiar with Lotus spreadsheets, Gnumeric recognizes the commercial at symbol (@) as the beginning of a formula and substitutes an equals sign. The plus and minus characters (+ and -) may also start formulas that involve calculation, but when used in front of a single number only indicate the sign of the number.

The simplest formulas just use the standard math operator and symbols. Addition, subtraction, multiplication, and division are represented by +, -, *, and /, just as you would expect. +,- can be placed in front of numbers to indicate sign, as well.

Example 5-1Examples of standard operators
```=5+5            returns 10.

=5-4            returns 1.

=-5             returns -5.

=5*5            returns 25.

=(5*5)+11       returns 36.

=(5*5)+(49/7)   returns 32.
```

Formulas can result in error values in several instances. If a formula is entered incorrectly, Gnumeric will display a warning and allow either the formula to be corrected or will save the formula as text for editing later. If a syntactically correct formula results in a nonsensical calculation (for instance, a division by zero), then an error value will be displayed indicating the error.

5.2.4.2. Using Functions

Formulas can also contain functions which denote the use of standard mathematical, business, statistical, and scientific calculations. These functions take the place of any data element in a formula and can therefore be combined with the standard arithmetic operators described above.

These functions have the form:

Example 5-2Basic Function syntax
```FUNCTIONNAME(ARGUMENTS)
```
where FUNCTIONNAME indicates the name of a function and ARGUMENTS indicates one or more arguments to the function. The function arguments are separated by commas (,).

While the documentation generally refers to functions and to cells in capital letters, their use is not actually case sensitive.

Some examples of the use of functions are:

Example 5-3Some examples of function syntax
```=SUM(A1,A2,A4,B5)

=AVERAGE(A1:A16)

=EXP(1)

=PI()

=3+4*MIN(A1,A2,B6)
```
The arguments of the functions vary in number from none, as in the PI() function, to an unlimited number, as in the SUM() function, depending on the type of function.

5.2.4.3. Cell Referencing

Formulas can include the displayed data from other cells. These contents are described as `cell references' which are names indicating that the contents of other cells should be used in the calculation.

Each cell in a spreadsheet is named by its column and row labels. The column labels are letters and the row labels are numbers. The first cell, therefore, is called A1. One column over and two rows down from cell A1 is the cell B3. The right most and bottom most cell is cell IV65536 which is the cell in column IV and in row 65536.

The value of a cell can be used in a formula simply by entering its name where a number value would otherwise occur. For example, to have the data in cell B1 appear in another cell, enter =B1 into that cell. Other more complex examples include:

Example 5-4Some examples of simple cell reference syntax
```=A1+EXP(B1)-(C3/C4)

=COS(A2)*SIN(A2)
```

5.2.4.3.1. Absolute cell referencing

Cells can be referenced in the default way (relative referencing), or by using absolute referencing. Absolute referencing means that when the cell is copied, the cell reference does not change. Normally, auto-filling a cell range or moving cell will change its cell reference to so that it maintains a relation to the original cell. Absolute referencing prevents these changes.

When Does Relative Referencing Make a Difference?

The difference between absolute and relative cell references only matters if you are copying or moving cells that contain cell references. For cells that are going to remain in place, both the relative and absolute references have the same result.

Example 5-5Relative References

For example, if =A1 is the formula entered into cell B2, cell B2 will display the data in cell A1, which is one row up and one column left. Then, if you copy the contents of B2 to cell F6, cell F6 will contain the value from E5, which is also one row up and one column left.

For the copied cell to still refer to A1, specify absolute references using the \$ character: \$A\$1 refers to cell A1, no matter where it is copied.

The format for absolute cell referencing is to use a '\$' in front of the cell coordinate that the you want to stay constant. The column, row, sheet, or any combination of these can be held constant.

Example 5-6Absolute cell referencing examples

What happens when a given formula is entered into cell B2, then copied to other cells?

=A1

=A1 is a normal, or relative, cell reference function. When =A1 is entered into cell B2, it refers to the value of data one cell up and one cell left from the cell with the reference. Therefore, if this formula were copied from cell B2 to cell C2, the value displayed in cell C2 will be the value of data in cell B1. Copied to cell R19, the formula will display the data in cell Q18.

=\$A1

In this case, the column value is absolute, but the row value is relative. Therefore, if =\$A1 is entered into cell B2, the formula refers to the data in column A that is one row up from the current location. Copied to cell C2, the formula will refer to the data in cell A1. Copied to cell R19, it will refer to the data in A18.

=A\$1

This formula uses a relative column value and an absolute row value. In cell B2, it refers to cell A1 as the data in the cell one column left and in row 1. Copied to cell C3, the formula will display the data in cell B1.

=\$A\$1

No matter where this formula is copied, it will always refer to the data in cell A1.

5.2.4.3.2. Referencing multiple cells

Many functions can take multiple cells as arguments. This can either be a comma separated list, an array, or any combination thereof.

5.2.4.3.2.1. Multiple individual cells

A comma separated list of cell references can be used to indicate cells that are discontinuous.

Example 5-7Some examples of function syntax
```=SUM(A1,B2,C4)

=MIN(A1,B2,C4,C5,D6)
```

5.2.4.3.2.2. Referencing a continuous region of cells

For functions that take more than one argument, it is often easier to reference the cells as a group. This can include cells in sets horizontally, vertically, or in arrays.

The ':' operator is used to indicate a range of cells. The basic syntax is upper left corner:bottom right corner.

Example 5-8Referencing blocks of cells
```=SUM(A1:E1)

=AVERAGE(B4:E7)

=MIN(A1:A5)
```

5.2.4.3.2.3. Referencing non-continuous regions

For referencing cells that are in non-continuous regions, you can use any combination of the above methods to get the needed cells.

Example 5-9Referencing blocks of cells
```=SUM(A1:E1, B19, L14:L17)

=AVERAGE(A1,A3, A5:C5)
```

5.2.4.3.3. Referencing cells on other sheets

It is possible to reference cells which are not part of the current sheet. This is done using the SHEETNAME!CELLLIST syntax, where SHEETNAME is an identifier (usually a sheet name) and CELLLIST is a regular cell reference as described in the previous sections.

Note that if SHEETNAME contains spaces, you need to quote the whole name to allow Gnumeric to group the separate words in SHEETNAME as single name. For example, you should use 'Name With Spaces'.

Example 5-10Referencing cells in other sheets
```='Sheet 0'!A1+'Sheet 3'!A5

=SUM('Sheet 1'!A1:'Sheet 1'!A5)
```

5.2.4.3.4. Referencing cells on other files

It is possible to reference cells in other files. The canonical form for these references is =[filename]SHEETNAME!CELLLIST. Note that, even if the name of the file contains spaces, you may not surround the filename with quotes, since these quotes would be interpreted as part of the filename.

Example 5-11Referencing cells in other files
```=[Name of the file]'Sheet 0'!A1

=CEIL( [First Version.gnumeric]'Sheet 1'!E20 )
```

5.2.4.4. Names

Names are labels which have a meaning defined by the user. Names can be defined to apply to a whole workbook or to a particular sheet in a workbook. Names can refer to a numeric value, to a particular range of cells, or to part of a formula. The name can be used wherever its meaning could otherwise be used.

Example 5-12Examples of name usage

If myCellRange is defined as '\$A\$1:\$B\$500' and my_E_Constant is defined as 2.71828182845 then we can have:

```=VLOOKUP(C1, "gnu", myCellRange, 2, 0)

=LN(my_E_Constant)

=SUM(myCellRange, my_E_Constant)
```

Names are defined using the Insert Name dialog. This allows manipulation of all the names in the related workbook. A name can have any form except that it cannot consist of a number, of the name of a sheet or of that of a function, since this would cause confusion.

In addition to the names defined by the user, Gnumeric has some pre-defined names for useful elements. These are:

• "Sheet_Title:" this returns the name of the current sheet

5.2.4.5. Array Formulas

It is periodically useful or necessary to have an expression return a matrix rather than a single value. The first example most people think of are matrix operations such as multiplication, transpose, and inverse. A less obvious usage is for data retrieval routines (databases, realtime data-feeds) or functions with vector results (yield curve calculations).

Example 5-13Entering an Array Formula

An array formula is currently entered by selecting the single range in which to store the result, entering the array formula, and hitting the key combination, Ctrl+Shift+Enter.

The result is displayed as:

```={FUNCTION(ARGUMENTS)}(num_rows, num_cols)[row][column]
```

5.2.4.6. Database Formulas

Solely for compatibility with Excel and ODF files, Gnumeric supports various database functions: DAVERAGE , DCOUNT , DCOUNTA , DGET , DMAX , DMIN , DPRODUCT , DSTDEV , DSTDEVP , DSUM , DVAR and DVARP .

Since these functions are quite restrictive on the criteria that can be used, it is often easier to use array functions as described in Section 5.2.4.5 ― Array Formulas. Array functions are also useful in the case that a specific database function does not exist:

Example 5-14Simulating DMEDIAN with an Array Function

As shown in Figure 5-2, instead of using (the non-existing) function DMEDIAN one can use the alternative expression median(if(A1:A20="AA",B1:B20)) entered as an array function as described in Section 5.2.4.5 ― Array Formulas. Multiple conditions can be combined using multiplication to obtain AND and addition to obtain OR as in median(if((A1:A20="AA")+(C1:C20="BB"),B1:B20)). Using defined names as introduced in Section 5.2.4.4 ― Names for A1:A20 and B1:B20 can make this code very flexible and readable.

In this case we can not use use if(OR(A1:A20="AA",C1:C20="BB"),...) since the OR function would be applied to all 40 equality tests rather than each of the 20 pairs of equality tests.

Figure 5-2Calculating the MEDIAN of Some Data Values