#### The strength in spreadsheets is the ability to use vectors to cause one cell to affect another cell or cells ### Numeric values in a worksheet can be added, subtracted, multiplied or divided by creating formulas Use this example for formula demonstrations. All formulas start with the = sign, and are followed by cell addresses and mathematical operators.

= begins a formula. If the = sign is not present, formula entries are treated as text

(parentheses) are to establish precedent. The result is held as a subtotal for further calculations. Where parentheses are enclosed within parentheses, they are calculated from the inside set to the outside set.

cell reference is a location on the sheet containing a value to work with. Unless fixed by user input, all locations are vectors, relative to the location of the cell that contains the formula.

if you need to lock a formula reference to a specific location (or, said another way, to an absolute - not relative - cell reference), use the f4 key to append the \$ sign to a cell reference to lock in a specific location

example:

• \$A1 refers ab\$olutely to row A but only relatively to column 1
[column will vary]
• \$A\$1 refers ab\$olutely to row A and ab\$olutely to column 1

#### arithmetic operators

• - subtraction
• * multiplication
• / division
• ^ exponentiation

a numeric constant is a number that will remain the same until the value is changed in the future

#### functions

are terms that describe built in formulas that perform specialized arithmetic

range reference is the starting and ending cells of a group of cells and enclosed in parentheses. they are separated by colons if the cells are contiguous, or with a comma if they are non-contiguous

[top]

### Entering a Formula

To enter a formula in a cell, click on the cell, then transfer your attention to the formula bar Click or type the = character, and then enter the formula. Remember to start all formulas and functions with the = sign.

Normally, formulas use cell references rather than numbers

• place the cell address of the cell that contains the number you want into the formula. That way, if the number changes, the formula remains correct.
• for example, =A1+B1 would add the numbers in cells A1 and B1 and put the answer wherever you entered this formula
• if you use cell addresses, you can change data later (e.g., data in B4), and then the number calculated by a formula will change automatically

However, you can use numbers in formulas

= D8*1.06
means
this formula equals
the contents of D8 multiplied by 1.06

When using the formula bar

• you can point to the cell you want
• type an operator
• point to the next cell
• OR enter the formula using the cell reference addresses

[top]

### Order of Precedence & Operations

(parentheses)
the result is held as a subtotal for further calculations. Where parentheses are enclosed within parentheses, they are calculated from the inside set to the outside set.

^ exponentiation,
/ division or
* multiplication
are calculated in the sequence entered from left to right

##### formulas are calculated left to right and parentheses are used to distinguish order

using 15/3+2 for example:

• 15/3+2 = 7
• 15 divided by three = five, and five plus two = seven
• or (15/3) + 2 = (5)+2 = 7

but

• fifteen divided by the result of three plus two = fifteen divided by five = three
• or 15/ (3+2) = 15/ 5 = 3

- subtraction
are calculated in the sequence entered from left to right

functions are calculated separately and included in the result depending on the adjacent arithmetic operators

[top]

### Edit Formulas

click in cell with formula or in the formula box The same is true in Excel for Mac [top]

### Techniques to Help Correct Errors in Data

Error Codes

• #### data is too wide for cell
• #N/A omitted a required argument in a function
• #REF the formula cannot find the cell included in the argument
• #VALUE the formula calls for a number or a value and the cell contains text

[top]

### Let the program work for you

to check for source of errors, use the Auditing Toolbar The same is true in Excel for Mac [top]