Value Added | daily

Class Schedule

Basics | sessions 01-03
1. 19 Jan intro and clients | lecture | labs
2. 26 Jan servers and command line | lecture | labs
3. 02 Feb networks and protocols | lecture | labs
Web Development | sessions 04-08

1. 09 Feb structural layer | lecture | labs
2. 16 Feb presentational layer | lecture | labs
3. 23 Feb using a structure | lecture | labs
4. 02 Mar behavioral layer | lecture | labs
5. 09 Mar design thoughts | lecture | labs
Dealing with Markup | sessions 09-10
1. 16 Mar control objects and display | lecture | labs
2. 23 Mar tools that read markup | lecture | labs
Working with data | sessions 11-14
1. 30 Mar formulas, functions, vectors | lecture | labs
2. 06 Apr data display | lecture | labs
3. 13 Apr manipulate data sets | lecture | labs
4. 20 Apr relational data bases | lecture | labs
Presentations | sessions 15-16
1. 27 Apr designing a presentation | lecture | labs
2. 04 May delivering a presentation | lecture | labs

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.

### The strength in spreadsheets is the ability to use vectors to cause one cell to affect another cell or cells ### This Microsoft Getting Started with Formulas worksheet will be useful

#### 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 column A but only relatively to row 1
[row will vary]
• \$A\$1 refers ab\$olutely to column A and ab\$olutely to row 1
[there is nothing relative about this reference]

arithmetic operators

• + addition
• - 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

### 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 equalsthe contents of D8multiplied by1.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

### 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

+ 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

### Edit Formulas

click in cell with formula or in the formula box The same is true in Excel for Mac ### 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

### Let the program work for you

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

30 March lecture | preps | spreadsheets | formulas | functions