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.
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.
|
Most of the standard math, business, statistical, and scientific calculations are implemented in terms of functions. Functions are in the form of:
Example 1-2. Basic Function syntax
=FUNCTION(ARGUMENTS)
|
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 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