SILS iSchool

05 Mar 2019

meets Tuesday and Thursday from 0800-0915

in Carolina Hall 220

Contact options

office hours in Manning 112


Value Added | daily

Class Schedule

Basics | sessions 01-05

10 Jan | intro
15 Jan | clients
17 Jan | servers
22 Jan | networks
24 Jan | basics lab

Web Development | sessions 06-11

29 Jan | structural layer
31 Jan | presentational layer
05 Feb | working with layers
07 Feb | behavior layer |
12 Feb | images & design
14 Feb | website lab

Document Markup | sessions 12-14

19 Feb | document markup
21 Feb | graphics
26 Feb | document markup lab

Spreadsheets | sessions 15-19

28 Feb | spreadsheets
05 Mar | formulas | functions | all functions | practice exercises | next session
07 Mar | data display

 09-17 Mar | Spring Break 

19 Mar | database tools
21 Mar | spreadsheets lab

Relational Database | sessions 20-26

26 Mar | relational databases
28 Mar | tables
02 Apr | relationships
04 Apr | input & output
09 Apr | SQL
11 Apr | complex queries
16 Apr | databases lab

Presentation | sessions 27-30

18 Apr | presentation design
23 Apr | presentation delivery
25 Apr | presentation lab
30 Apr | 0800-1100 | final in class presentation





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

Dilbert cartoon, 20070808, by Scott Adams

Numeric values in a worksheet can be added, subtracted, multiplied or divided
by creating formulas

[image of a formula with descriptive labels]

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

back to top

Entering a Formula

To enter a formula in a cell, click on the cell, then transfer your attention to the formula bar

[MSExcel 2013 formulas can be entered in two locations]

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

back to 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

+ 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

back to top

Edit Formulas

click in cell with formula or in the formula box

[MSExcel 2013 places to enter formulas]

The same is true in Excel for Mac

[MSExcel 2013 formulas can be entered in two locations]

back to 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

back to top

Let the program work for you

to check for source of errors, use the Auditing Toolbar

[MSExcel 2007+ formula auditing tools]

The same is true in Excel for Mac

[MSExcel 2013 formulas auditing tools]

back to top