## 17 Oct 2018

Class Schedule

Basics | sessions 01-05

22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab

Web Development | sessions 06-11

12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab

Document Markup | sessions 12-14

03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab

17 Oct  |  formulas  | functions | all functions | practice exercises | next session

22 Oct | data display

18 Oct | Fall Break
24 Oct | database tools

Relational Database | sessions 20-26

31 Oct | relational databases
05 Nov | tables
07 Nov | relationships
12 Nov | input & output
14 Nov | SQL
19 Nov | complex queries
26 Nov | databases lab
21 Nov | Thanksgiving

Presentation | sessions 27-30

28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation

## School Information

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

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

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

- 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