CS 104 : Spreadsheets Fun with Formula

1
C-S 104 SpreadsheetsFun with Formula!
Image from http//belle.designwest.com/disney/nbc
/jack/formula.gif
2
Spreadsheet Basics

• A cell is the intersection of a row and column
• Each cell in the spreadsheet has a name
• The column-name followed by the row-name

C5
3
Spreadsheet Basics

• A cell can contain one of three kinds of data
• Value yes, were talking about numbers
• Formula yes, were talking about mathy thingys
• Formula are used to derive information (values or
  text) from already existing information (values
  or text)
• A cell containing a formula will display the
  value obtained by the formula not the formula
  itself!
• Text yes, were talking about everything else

Image from http//www.prenhall.com/phitcareers/cha
p2/images/formula.jpg
4
Formula

• A formula is a mathematical expression that
  combines existing data to produce a result.
• Formula always begin with an symbol
• Formula may contain values, cell-references,
  binary operators, parenthesis, and function
  calls.
• 10A5
• B1B2B3
• C9/B2
• Max(A1B3)10

5
Cell References in Formula

• consider the following formula
• 3 A1
• What does the A1 mean?
• A1 is a cell reference
• The value contained in cell A1 is substituted in
  the above expression
• What if cell A1 contains a
• 3
• a2
• 39
• 3a2

6
Binary Operators

• All operators take data as input (operands) and
  produce a result.
• Binary operators take two inputs and produce a
  result
• Binary operators in Excel include
• Addition () as in 10A5
• Subtraction (-) as in 10-A5
• Multiplication () as in 10A5
• Division (/) as in 10/A5
• Exponentiation () as in 10A5

7
Precedence of Operators

• Question what does the following formula
  produce?
• 10 35 2 2 1 10 / 5
• All operators have a precedence which determines
  the order of evaluation within a large formula
• Addition and Subtraction have lowest precedence
• Multiplication and Division have identical
  mid-level precedence
• Exponentiation has the highest precedence
• Rule for formula evaluation is
• Select the highest-precedence operator and
  evaluate
• If two operators have the same highest-precedence,
  choose the left-most operator
• Repeat until there are no more operators

8
Precedence of Operators

• Rule for formula evaluation is
• Select the highest-precedence operator and
  evaluate
• If two operators have the same highest-precedence,
  choose the left-most operator

10 35 2 2 1 10 / 5
10 35 4 1 10 / 5
10 140 1 10 / 5
10 140 1 2
150 1 2
149 2
147
9
Functions

• Formula may also contain function calls.
• A function is a way of processing input to obtain
  output.
• Excel has 100s of built-in functions. We will
  discuss the most commonly used.
• Function syntax is
• FUNCTION_NAME ( input1, input2, )
• Inputs are numbers, cell references, or ranges
  that the function needs in order to perform its
  job
• The entire expression represents the functions
  value (or output).

10
Functions

• Commonly used functions include
• AVERAGE( values )
• Computes the average value of the input numbers.
  Values is typically a range but may be a
  comma-separated list of items.
• MAX( values )
• Computes the maximum value of the input numbers.
  Values is typically a range but may be a
  comma-separated list of items.
• MIN( values )
• Computes the minimum value of the input numbers.
  Values is typically a range but may be a
  comma-separated list of items.
• SUM( values )
• Computes the sum of the input numbers. Values is
  typically a range but may be a comma-separated
  list of items.

11

• What should be entered into cells
• B13 to obtain classroom average for HW1?
• C13 to obtain classroom average for HW2?
• D13 to obtain classroom average for HW3?
• E6 to obtain the total points for Britney Spears?
• E13 to obtain the classroom average for all HW?

AVERAGE(B6B10)
AVERAGE(C6C10)
AVERAGE(D6D10)
SUM(B6D6)
AVERAGE(E6E10)
(4976948998)/5
12
Cell References in Formula

• When a cell reference occurs in a formula it may
  be a
• RELATIVE Reference
• 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 Reference
• Must be explicitly written (not the default)
• The cell reference is always the same, even if it
  is moved/copied to another cell.
• MIXED Reference
• Either the column-part or the row-part is
  RELATIVE while the other part in ABSOLUTE

13
Absolute vs. Relative

• To specify absolute referencing for part of a
  reference, prefix the row and/or column part with
  a
• For each of the following cell references,
  identify it as absolute, mixed, or relative.

Mode
Cell Reference Example
Relative (default)
A3
Absolute
A3
Mixed
A3
Mixed
A3
14
Absolute vs. Relative
????

• Using relative addressing, the formula changes
  when copied
• For example, copy the formula from cell A14 to
  cell C13
• The reference B11 in A14 MEANS the cell one col
  right and two rows up
• The reference B14 in A14 MEANS the cell one col
  right and zero rows up
• Since the MEANING is preserved when copying the
  formula must change

15
Absolute vs. Relative
????

• Using relative addressing, the formula changes
  when copied
• For example, copy the formula from cell A14 to
  cell C13
• The reference B11 in A14 MEANS the cell one col
  right in row 11
• The reference B14 in A14 MEANS the cell one col
  right in row 14
• Since the MEANING is preserved when copying the
  formula must change

16
Absolute vs. Relative
????

• Using relative addressing, the formula changes
  when copied
• For example, copy the formula from cell A14 to
  cell C13
• The reference B11 in A14 MEANS the cell in col B
  3 rows up
• The reference B14 in A14 MEANS the cell in col
  B in the same row
• Since the MEANING is preserved when copying the
  formula must change

17
Using Relative References

• Most times, a formula will be copied into a large
  number of adjacent cells in order to fill a
  spreadsheet.
• Complete the Cost to Coop, Revenue Amount,
  and Item Profit columns below!

18
Case Study

• A local computer company sells three types of
  products.
• Computer hardware
• Computer software
• Computer support contracts
• This company hires sales personnel and pays
  commission (in addition to hourly salary) on the
  amount of total sales generated. The commission
  is computed monthly.
• Computer Hardware at a 5 rate
• Computer Software at an 8 rate
• Computer Support at a 12 rate
• Write a spreadsheet to compute the total amount
  of commission paid by the company.

19
Case Study

• Input
• Commission rates for three product categories
• For each employee
• Employees name or identifier
• Months sales of computer hardware
• Months sales of computer software
• Months sales of computer support
• Output
• For each employee
• Commission for computer hardware
• Commission for computer software
• Commission for computer support
• Total bonus for each employee

20
How to Count?

• Would like to determine how many students
  received an A, B, C, D or F.
• What to enter into cell H15 to compute this value?

Image from http//www.flickr.com/photos/heather/11
032642/
21
COUNTIF(yes, countif)

• The COUNTIF function is used to count!
• Syntax COUNTIF( range, criteria)
• Range is a range of cells
• Criteria is a condition the cells value must
  meet in order to be counted
• May be a number, expression, cell reference, or
  text .
• For example, criteria can be expressed as 32,
  "32", "32", "apples", or B4.

22
How to Count?
23
Date Functions

• Commonly need to manipulate dates
• Excel represents a date as a single number
• The number of days since January 1st, 1900
• May be a real-valued number
• January 1, 2008 ? 39448

24
Date Functions

• Commonly used functions to manipulate dates
• DATE( year, month, day ) returns the integer
  for the date corresponding to year, month, day.
• DAY( date ) returns the day of the month for
  the specified integer-valued date
• MONTH( date ) returns the month number for the
  specified integer-valued date
• YEAR( date ) returns the year for the specified
  integer-valued date
• WEEKDAY ( date ) returns the day of the week
  for the specified integer-valued date (the day of
  the week is a number where SUNDAY 1, MONDAY
  2, etc)
• NOW() returns the real-valued date for today
• TODAY() returns the integer-valued date for now

25
SUMIF

• SUMIF used to sum elements in a range that
  satisfy a criteria
• sumif(key_range, criteria, sum_range)
• key_range range of cells to evaluate against the
  criteria
• criteria an expression or value that determines
  which elements to include in the summate
• sum_range cells to include in the sum if their
  corresponding key satisfies the criteria

26
SUMIF Example

• Given student financial aid information, compute
  the total assistance per state

27
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.

28
An Example of Financial Functions

• Must use the following time-value-of-money
  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.

29
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

http//www.wealth4freedom.com/dollar_bill_great_se
al.jpg
30
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

31
Example

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