Formulas & Functions

Formulas

A formula is an equation that analyzes data in cells.  A formula performs some type of operation.  Typically these are simple mathematical or analysis operations such as addition, multiplication, or averaging values.  A formula can refer to cells in the spreadsheet.  This is ultimately what makes a spreadsheet so powerful.  Rather than "hard-code" values into a calculation, you can enter a reference to a cell where the value is located.  Consider the following example.

 

A

B

1 $2.15 23.43
2 $5.25 34.56
3 =A1+A2 28.93
4   =AVG(B1..B3)

In this table, cell A3 contains the formula =A1+A2.  Rather than placing the actual values in the formula, references to the cells are placed in the formula.   This tells the program to add the contents of cell A1 to cell A2.  On the other hand, cell B4 contains a formula that uses the AVG function.  This function uses a range reference, rather than specific cell references.  This range tells the spreadsheet to use all the cells between cell B1 and B3 in its calculation. 

For more information about cell references and dependent formulas, see the Calculating Cells and Dependent Cells topic.

Formula Operators

Operators specify the type of calculation that you want to perform on the elements of a formula.  Gobe Productive includes four different types of calculation operators: math, comparison, text, and reference.

Math Operators

Math operators perform basic mathematical operations such as addition, subtraction, or multiplication; math operators also combine numbers and produce numeric results.

Math operator Meaning Examples
+ (plus sign) Addition 3+3, A1+A2
– (minus sign) Subtraction or Negation 3–1, A1-B1, -1.23
* (asterisk) Multiplication 3*3, A1*B1
/ (forward slash) Division 3/3, A1/A2
% (percent sign) Percent 20%
^ (caret) Exponentiation 3^2 (the same as 3*3), A1^B1 (value of A1 to the power of the value of B1)

Comparison Operators

Comparison operators compare two values and then produce the logical value TRUE or FALSE.

Comparison operator Meaning Examples
= (equal sign) Equal to A1=B1
> (greater than sign) Greater than A1>B1
< (less than sign) Less than A1<B1
>= (greater than or equal to sign) Greater than or equal to A1>=B1
<= (less than or equal to sign) Less than or equal to A1<=B1
<> (not equal to sign) Not equal to A1<>B1

Text Operators

The text operator "&" combines one or more text values to produce a single piece of text.

Text operator Meaning Examples
& (ampersand) Connects, or concatenates, two values to produce one continuous text value "Meat" & "loaf" produce "Meatloaf"

Reference Operators

Reference operators combine ranges of cells for calculations.

Reference operator Meaning Examples
.. (double periods) Range operator, which produces one reference to all the cells between two references, including the two references B5..B15
, (comma) Argument Separator, which separates individual references in a given argument.  SUM(B5..B15,D5..D15)

Operator Precedence

Operations are performed in a specific order.  If you combine several operators in a single formula, the spreadsheet performs the operations in the order shown in the following table.  If a formula contains operators with the same precedence, the like operators are evaluated from left to right.  To change the order of operator precedence, enclose one or more calculations in parentheses.  Operations within parentheses are always performed first. 

Precedence Operator(s) Description
1 .. (double period) , (comma) Reference operators
2 Negation (as in –1)
3 % Percent
4 ^ Exponentiation
5 * and / Multiplication and division
6 + and – Addition and subtraction
7 & Connects two strings of text (concatenation)
8 = < > <= >= <> Comparison

Functions

The Gobe Productive spreadsheet environment contains numerous predefined formulas called functions.  A function is a formula that performs a very specific operation.  For example, one of the more common functions is average or AVG.   If you enter the formula =AVG(B1..B40) the spreadsheet will take all the values from cells B1 through B40, add them up, and then average them and report the result.  Functions, such as AVG, can drastically speed up your ability to design and develop complex analysis and calculations.

Most functions require an argument to get something done.  For example, the AVG function requires a range of cells to average.  Without this argument, the function cannot return any values.  Naturally, more complex functions require more complex arguments.  Moreover, the arguments must be entered using a specific syntax.   The function reference contains the required arguments and syntax for all the functions in the Gobe Productive spreadsheet environment.


Copyright © 1999, Gobe Software, Inc