CS 104 : Spreadsheets Fun with Formula

1
C-S 104 SpreadsheetsFun with Formula!
Image from Kodama on www.flickr.com
2
An Example of Financial Functions

• Excel has built-in functions for financial
  analysis
• Develop a worksheet to determine the monthly
  mortgage payment amount given an initial loan
  amount, an annual interest rate, and length of
  term.

3
An Example of Financial Functions

• Must use the following function
• PMT( rate, nper, pv, fv, type )
• rate the interest rate per pay period
• nper the total number of pay periods
• pv the initial loan amount
• fv the future value of the loan (usually
  zero)
• type does the payment occur at the start (1)
  or end (0) of pay period. Defaults to end of
  period.

4
Example
Problem statement What is the goal of the
worksheet? Output What are the desired results?
Input What information is needed to calculate
the output? Formula How is the input related to
the output?

• Problem statement To determine the affordability
  of a mortgage loan
• Output(s)
• The monthly mortgage payment amount
• Input
• Annual interest rate
• Total number of years of the loan
• Loan amount
• Assumptions
• There are 12 pay periods in each year
• Fixed rate, fixed payment

http//www.wealth4freedom.com/dollar_bill_great_se
al.jpg
5
Example
Problem statement What is the goal of the
worksheet? Output What are the desired results?
Input What information is needed to calculate
the output? Formula How is the input related to
the output?

• Input
• Annual interest rate
• Total number of years of the loan
• Loan amount
• Formula
• PMT( rate, numberPeriods, presentValue )
• numberPeriods numberOfYears 12
• Rate periodic rate annual rate / 12

6
Example

• Label the assumptions
• Document the input cells
• Document the output cell

7
Relational Operators

• Relational operators produce logical values
• There are exactly two logical values
• True
• False
• Comparison operators are binary operators
• Each operand must be a number

8
Relational Operators

• Relational operators produce logical values

10 lt 30
30 30
30 ltgt 30
35 gt 30
A5 gt B5
9
Logical Functions

• Logical functions process logical values
• There are exactly two logical values
• True
• False
• Logical values can be combined using
• Conjunction (and)
• Disjunction (or)
• Negation (not)

10
Logical Functions

• Logical functions include
• AND( input1, input2, input3, )
• Each input value must be either true or false.
  Must have at least 1 input.
• The result of the function TRUE if all inputs are
  true and FALSE otherwise
• OR( input1, input2, input3, )
• Each input value must be either true or false.
  Must have at least 1 input.
• The result of the function FALSE if all inputs
  are false and TRUE otherwise
• NOT( input )
• The input must be either true or false
• The result of the function is TRUE if the input
  is false and FALSE otherwise
• TRUE()
• The result of this function is TRUE
• FALSE()
• The result of this function is FALSE

11
Logical FUNCTIONS

• In SQL, logical combinators were OPERATORS
• The OR always has TWO operands and the word
  itself occurs between them
• SELECT supplier_idFROM supplierWHERE
  supplier_name 'IBM' OR supplier_city
  'Newark'
• In Excel, logical combinators are FUNCTIONS
• The OR is a function the word itself occurs
  first and may have 1 or more arguments
• OR( A5 gt 10, B5 gt 10)

12
Example of Logical Functions/Operators

• Write a spreadsheet to determine admission for
  college applicants
• Students are admitted only if
• High school GPA exceeds or equals 3 AND
• Math SAT exceeds or equals 600

13
Example

• Best to consider the cut-off values as input
• What to enter into D7?

AND(B9gtB5, C9gtB6)
14
Example

• Best to give names to cells containing referenced
  data.
• What to enter into D7?

AND(B9gtMIN_GPA, C9gtMIN_SAT)
15
Conditionals

• Often need to make decisions based on a criteria
• For example a company contributes 4 of an
  employees salary to health care if they are full
  time and makes no contribution if they are part
  timers.

16
Conditionals

• The IF function is used to select exactly ONE
  value from exactly TWO choices
• IF( criteria, ifTrue, ifFalse )
• Criteria must be a logical (boolean) value
• Function returns ifTrue if criteria is true and
  ifFalse otherwise

IF(10gt5, YES, NO)
IF(10gt20, 30, 50)
IF(A510, B5, C5)
17
Conditional Example

• What to enter into cell E10?

IF(B10PT, 0, C10.04)
18
Conditional Example

• A fixed bonus is given to any member of the sales
  force that sells a minimum amount of hardware and
  support.
• Cell F9 contains what formula?

19
Conditional

• Can use IF function to select among multiple
  choices
• Use nested ifs
• To select either A, B, or C (three choices)
• IF(CONDITION1, IF(CONDITION2, A, B), C)

20
Conditional

• IF(CONDITION1, IF(CONDITION2, A, B) , C)

21
Conditional Example

• Sometimes need to make a choice in a choice!
• What to enter into E10?

IF(B10PT, 0, IF(D10F,C10.04, C10.03))
22
Conditional Example

• Better to make the percentages inputtable (i.e.
  they may change!)
• What to enter into E10?

23
Deleting / Inserting Cells

• Formula may contain
• cell references A5
• cell ranges SUM(A5F20)
• When a cell is deleted
• any formula containing a reference to the deleted
  cell is broken since the cell no longer exists
• any formula containing a range where the cell is
  located is (potentially) modified

24
Deleting / Inserting Cells

• Consider the formula

A2B2C2

• What happens to the formula when cell B2 is
  deleted?

A2REF!C2

• REF! is an error message indicating that
  there is an error with the formula. (An invalid
  reference)

25
Deleting / Inserting Cells

• When a formula contains a range, the range is
• modified if an entire row or an entire column of
  the range is deleted
• unchanged if any portion otherwise is deleted

26
Deleting / Inserting Cells

• Consider the formula

SUM(A2F10)

• What happens to the formula when row 5 is deleted?

SUM(A2F9)

• The range is modified since an entire row of the
  range was deleted.

27
Deleting / Inserting Cells

• Consider the formula

SUM(A2F10)

• What happens to the formula when cell B2 is
  deleted?

SUM(A2F10)

• The range is not modified since the deleted part
  was not an entire row or column of the range was
  deleted.

28
Deleting / Inserting Cells

• Consider the formula

SUM(A2F10)

• What happens to the formula when a new row is
  inserted between rows 3 and 4?

SUM(A2F11)

• The range is modified to include the inserted row
  of data.

29
Deleting / Inserting Cells

• Consider the formula

SUM(A2F10)

• What happens to the formula when a new column is
  inserted between cols B and C?

SUM(A2G10)

• The range is modified to include the inserted
  column of data.

30
Formatting Data

• Data can be presented (or formatted) in many ways
• Presentation (or formatting) has to do with the
  way the data is displayed.

Phone Number Database
31
Formatting

• Cells can be formatted
• Cells contain data
• Value
• Text
• Formula which produce a text or value
• Cells display the data as instructed by formatting

32
Formatting
Lets say a cell contains the value 39183
Apply the following formatting style
The cell displays the data accordingly
39183
right-aligned general number
39183
33
The Format Cells dialog box
34
The Alignment tab of the Format Cells dialog box
35
Merge a range of cells

• To merge a range of cells into a single cell
• Use the Merge option on the Alignment tab in the
  Format Cells dialog box
• To split a merged cell back into individual
  cells
• Select the merged cell
• Click the Merge and Center button again
• Or uncheck the Merge Cells check box on the
  Alignment tab in the Format Cells dialog box

36
Merge headings across multiple cells
37
Hide rows and/or columns

• You can hide rows or columns
• Does not affect the data stored there
• Does not affect any cell that might have a
  formula reference to a hidden cell
• To hide a row or column
• Select the row or column and then choose Hide
  from the shortcut menu that pops up when you
  right click the row or column heading
• To unhide a row or column
• Select the headings of the rows or columns that
  border the hidden area, then choose Unhide from
  the shortcut menu that pops up when you right
  click the row or column heading

38
Worksheet with hidden cells
39
Create and apply styles

• If you have several cells that use the same
  format, you can create a style for those cells.
• A style is a saved collection of formatting
  options number formats, text alignment, font
  sizes and colors, borders, and background fills.
• If you modify the specifications for a style, the
  appearance of any cell associated with that style
  would be automatically changed to reflect the new
  style.

40
The Style dialog box
41
Use Find and Replace to change formats

• Can find cells based on formatting and replace
  formatting
• Click Edit on the menu bar and then click Replace.