Code and data

1. Code and data http://www.flickr.com/photos/riebart/4625960192/sizes/l/in/photostream/ No magic formula

2. Fixed Program Architecture • Early computers used a fixed-program architecture. • The instructions were expressed at the hardware level. • The data was stored in memory • Modern computers use a stored-program architecture. • Both instructions and data are stored in memory • Von Neumann Architecture • The first stored-program machine • Stored-program machines can write self-modifying code

3. Spreadsheets • Spreadsheet – an interactive program designed to organize, process, and display data in tabular form. • Spreadsheets treat code and data in a similar way. • formula are instructions that generate data

4. Structure • Worksheet: a tabular arrangement of cells • a single cell is located at the junction of a row and column of the sheet. • Rows are numbered starting from 1 • Columns are alphabetical and start at ‘A’. • Cell: a box, that contains data. • Each cell has a unique name • Roughly corresponds to a variable in a programming language. • Name is formed by splicing together column and row.

5. Cell References • A cell reference is either • RELATIVE • Default. References are interpreted with respect to the containing cell • When moving/copying relative cell reference, the cell reference changes by the amount moved • ABSOLUTE • Denoted with a $ preceding col, row or both • The cell reference is always the same, even if it is moved/copied to another cell. • MIXED • Either the column-part or the row-part is RELATIVE while the other part in ABSOLUTE

6. Absolute/Relative/Mixed Modes Cell Reference Example Mode A3 Relative (default) $A$3 Absolute $A3 Mixed A$3 Mixed

7. Cells • A cell can contain • A number • A logical value • text • A formula. • A formula is used to generate new data from data that already exists • If a change is made to anycell, allcells in the spreadsheet will be updated • Any cell that contains a formula will display the data computed by the formula and not the formula itself. • A formula is the primary computational element. A formula expresses the relationship between the input datum and the data generated by the spreadsheet.

8. Formula • We can define a formula as follows • A number is a formula • A cell reference is a formula • A logical value is a formula • Given two formula E1 and E2, E1 OP E2 is also a formula where OP is one of: • +, -, *, /, ^ • <, <=, =, <>, >, >=

9. Formula

10. Formula

11. Arity • The arity of an operator is the number of inputs

12. Precedence • Any formula involving at least two different operators refers to the precedence table to determine which operator to apply. • For a formula that has two different operators of equal precedence we choose the left-most operator.

13. Associativity • Associativity is used to select the order of evaluation when a formula has two-or-more identical operators. • Left associativity: evaluate left-to-right • Right associativity: evaluate right-to-left • 1-2-3

14. Example • Consider evaluating • 1+3^2*2-10/5

15. Formula structure • A computer understands that a formula is a tree

16. Formula and Cell References • The value of a cell-reference is determined by • If the cell contains data: that is the value • If the cell contains a formula: the data produced by the formula is the value • Consider the formula 3 * A2 • If A2 contains the value 2 • If A2 contains the formula 3*6 • If At contains the formula B3*C3

17. Example • Three classmates keep track of their Facebook friends

18. Dependency • Most formulas depend upon other cells. • E2 cannot be computed until the formula for D2 has produced a value. • Spreadsheets must determine the order in which formula must be evaluated by analysis of the data dependencies of the spreadsheet. • A formula is dependent upon another cell if the formula contains a reference to that cell. • Cells A2, B2 andC2 are not dependent upon any other cell • Cell D2 depends on A2, B2 and C2 • E2 depends on D2 • Also note that since E2 depends on D2 and since D2 depends on A2, B2 and C2 we can conclude that E2 depends on each of these other cells.

19. Functions • A function is a named sequence of instructions that will produce an output value when it is provided with input. • Some functions may produce values even when no inputs are provided • Functions are used by typing in the name of the function followed by a comma-separated list of input values. • Values are in parenthesis to indicate that they are grouped together as inputs • The value of the function call is the value produced for the values that are fed into the function.

20. Functions and Ranges • A range is a block of cells. A ranged is denoted as • UPPER_LEFT:LOWER_RIGHT • UPPER_LEFT is a cell reference • LOWER_RIGHT is a cell reference • Functions • max(range) : computes the maximum value in the range • min(range) : computes the minimum value in the range • average(range) : computes the average of the range • sum(range) : computes the sum of the range

21. Functions

22. Example

23. Example ? =(49+76+94+89+98)/5

24. Logical Functions • For each of the following functions, each input must be a logical value (TRUE or FALSE) • AND(v1, v2, …, vn) • TRUE if all inputs are true and FALSE otherwise • OR(v1, v2, …, vn) • FALSE if all inputs are false and TRUE otherwise • NOT(v1) • TRUE if the input is FALSE and TRUE otherwise

25. Example • A college accepts all students who • HS GPA of at least 3.0 • Have either • ACT COMPOSITE of 25 or greater • SAT COMPOSITE of 1800 or greater

26. Conditionals • IF(CONDITION, E1, E2) • CONDITION is a boolean • E1 and E2 are formula/value • The IF function returns • E1if CONDITION is TRUE • E2 if CONDITION is FALSE • Examples: • IF(A3 > 10, A4, A5) • IF(10 <> A5, 1, 0) • IF(A1>=A2, IF(A1>=A3, A1, A3), IF(A2>=A3, A2, A3))

27. Example

28. Example

29. Example • A fixed bonus amount is given to any member of the sales force that sells a minimum amount of hardware and support

30. Example

31. Lookups • A lookup table is a range in which data is accessed by a key. • Example: • John Bunyan gets a 73.5% score. • What grade does he receive? • What is the key? • What is the data?

32. Lookups • Exactly what does this table mean? • Any score between 0 up to 60 receives an F • Any score between 60 up to 70 receives a D • Any score between 70 up to 80 receives a C • Any score between 80 up to 90 receives a B • Any score above 90 receives an A • Even though the number 73.5 doesn’t appear in the table, it can be interpreted to be in the table implicitly since each key entry represents a range of values.

33. Lookups • Exactly what does this table mean? • What is the key • What is the data • What is the meaning • Is the meaning the same as the previous table? • Any department between MIS and C-S has REMUS as the chair?

34. VLOOKUP • VLOOKUP is used to find data in a table • Uses a key to find other data • VLOOKUP(KEY, TABLE, COL, IS_RANGE) • KEY : the key • TABLE : a range. Must be • At least two columns wide • Sorted in ascending order • COL : an integer. The column OF THE TABLE that contains the data • IS_RANGE : a boolean describing ranged or exact table interpretation. • TRUE for range • FALSE for exact • If it is omitted, an exact match is returned. If an exact match is not found, the largest value less than the key is returned

35. Example

36. Example