Formulas

Formulas are the key to the whole ball of wax when it comes to spreadsheets. A formula can take almost anything as its arguments including single numbers, cell references, ranges of cells, arrays of cells, etc.

Syntax

Formulas are distinguished from regular data by a '=' as the first character. Everything following a '=' is evaluated as a formula, not a string.

The simplest fomula's just use the standard math operator and symbols. +,-,*,/ are intrepreted just as you would expect them to. +,- can be used as unary operators and indicate sign, just as can be expected.

Example 1-1. Examples of standard operators

	=5+5            returns 10.
	
	=5-4            returns 1.
	
	=-5             returns -5.
	
	=5*5            returns 25.
	
	=(5*5)+11       returns 36.
	
	=(5*)+(49/7)    returns 32.
      

Using Functions

Most of the standard math, business, statistical, and scientific calculations are implemented in terms of functions. Functions are in the form of:

While the documentation generally refers to functions in all caps, there use is not actually case sensitive.

Example 1-3. Some examples of function syntax

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

Where a function takes multiple arguments such as the SUM example, the number of arguments effectively unlimited.

Names

Names help to simplify complex formulae. A name is assinged to a formula which will be evaluated when it is referred to in another formula. Names are particularly useful for labeling cell ranges.

Example 1-4. Examples of name usage

If DataBase is defined as '$A$1:$B$500' and E_Constant is defined as 2.71828182845 then we can have:

	    =VLOOKUP (C1, "gnu", DataBase, 2, 0)

	    =LN(E_Constant)

	    =SUM(DataBase, E_Constant)
	  

Names are defined using the Insert Name dialog. This allows manipulation of all the names in the related workbook. A name is restrained from taking that of a function, since this would cause confusion.

In addition to the names you define there are built in names:

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 1-5. Entering 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 magic combination, Ctrl-Shift-Enter.

The result is displayed as :

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