Formula Elements

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.

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.

=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.

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:

FUNCTIONNAME(ARGUMENTS)
        

Some examples of the use of functions are:

=SUM(A1,A2,A4,B5)
	    
=AVERAGE(A1:A16)
	    
=EXP(1)
	    
=PI()
	    
=3+4*MIN(A1,A2,B6)
	  

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:

=A1+EXP(B1)-(C3/C4)
      
=COS(A2)*SIN(A2)
        

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
• 5.2.4.3.2.2. Referencing a continuous region of cells
• 5.2.4.3.2.3. Referencing non-continuous regions

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-7 Some 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-8 Referencing 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-9 Referencing blocks of cells

=SUM(A1:E1, B19, L14:L17)
	  
=AVERAGE(A1,A3, A5:C5)
	  

='Sheet 0'!A1+'Sheet 3'!A5

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

=['Name of the file']'Sheet 0'!A1

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

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.

=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:

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).

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