preps | spreadsheets | formulas | functions
Class Schedule
This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
home & schedule | syllabus | contact | grades
Formulas are mathematical statements that apply to a cell,
or, through vectors, to a range of cells.
They can also apply to cells in different worksheets or different workbooks.
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:
arithmetic operators
a numeric constant is a number that will remain the same until the value is changed in the future
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
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
However, you can use numbers in formulas
=D8*1.06
means
this formula equalsthe contents of D8multiplied by1.06
When using the formula bar
(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
using 15/3+2 for example:
but
+ addition or
- 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
click in cell with formula or in the formula box
The same is true in Excel for Mac
Error Codes
to check for source of errors, use the Auditing Toolbar
The same is true in Excel for Mac
30 March lecture | preps | spreadsheets | formulas | functions