More_ Spreadsheets

							 More Spreadsheets

Range Formulas & Empty Cells
     Logical Functions
     Lookup Functions
          Range Arguments
• Can all math functions be used with a
  range argument?
  – NO! May get error or may get one value,
    depending on where the formula is entered
    relative to the range.
  – Correct—Enter formula in terms of first cell in
    range, then copy/paste to “iterate”
  – (see emptys.xls in examples/solutions)
Ranges that include different types
              of cells
• How do mathematical functions such as
  sum, max, average, count treat cells that
  are empty or have text?
• What do the functions averageA, countA,
  maxA do instead?
     Relational Operators
<              “less than”

<=             ‘less than or equal to”

>              “greater than”

>=             “greater than or equal
               to”
=              “equal to”

<>             “not equal to”
      Logical Combinations
• And(logical1,logical2,…) produces TRUE
  when all are true, otherwise FALSE
• OR(logical1,logical2,…) produces FALSE
  when all are false, otherwise TRUE
  (“inclusive or”)
• NOT(logical1) produces TRUE when
  logical1 is false, and produces FALSE
  when logical2 is true
                             The IF Function

•   IF(logical expression,
    value_when_true,
                                            A   B   C   D
    value_when_false)

                                        1   2   3   7
•   =If(B3>5,C2^2,”Carl”) put in cell
    D2 would cause D2 to display 64
•   Copy & paste to cell D1 would
    cause D1 to display ?
                                        2   5   4   8

                                        3   1   6   9

                                        4
       Information Functions
• ISBLANK(cell) is TRUE when cell is
  empty
• ISTEXT(cell) is TRUE when cell has text
  – Note the difference between ISTEXT(“1”) and
    ISTEXT(1)
• ISNA(value) is TRUE when value is the
  #N/A error value
                COUNTIF
• COUNTIF(range, condition to be counted)
  – =Countif(a3:a12,5) returns the number of
    times 5 appears in the range
  – =countif(a3:a12,”Bob”) returns the number of
    times Bob appears in the range
  – =countif(a3:a12,D3) returns the number of
    times the value in D3 appears in the range
  – =countif(a3:a12,”>5”) returns the number of
    times a value greater than 5 appears in the
    range
                COUNTIF
– =countif(a3:a12,”=5”) returns the number of times 5
  appears in the range
– =countif(a3:a12,”=D3”) does NOT return the number
  of times the value in D3 appears in the range
– =countif(a3:a12,”>D3”) does NOT return the number
  of times a value greater than the value in D3 appears
  in the range
– =countif(a3:a12,”>”&D3) retuns the number of times a
  value greater than the value in D3 appears in the
  range
                   SUMIF
• SUMIF(range,criteria,sum_range)
  – Range is the cells to examine for meeting the
    criteria
  – Criteria is the condition to be met
  – Sum_range is the corresponding cells to add
    when criteria is met
• =sumif(a2:a5,”>0”,b2:b5) adds those cells
  in b2 thru b5 whose corresponding a2 thru
  a5 value is positive
                        SUMIF
• The same limitations as countif apply with regard to the
  criteria including relational operators and cell references

• =sumif(a2:a5,”>c3”,b2:b5) does NOT correctly add
  those cells in b2 thru b5 whose corresponding a2 thru a5
  value is greater than the value in cell c3
• (see countif.xls in examples)
   Table Lookups & References
• Vlookup & HLookup—used to look for a “target” in first
  column or row of a table & return a “corresponding”
  value from another column or row of the table
• Lookup—used to look for a “target” in a “vector” (single
  row or column) & return a value in the corresponding
  position in another “vector” (single row or column)
• Match—used to find the relative position of a “target” in a
  vector
• Index—used to return the value in a specified relative
  position in a table
           VLookup & Hlookup Specifics

• First argument is target (what you are looking for)
• Second argument is range in which you are looking (limited to
  leftmost column of range for vlookup, topmost row of range for
  hlookup)
• Third argument is relative column(for vlookup) or row (for hlookup)
  from which corresponding value is to be returned. Must have been
  included in range.
• Fourth argument is TRUE or FALSE, used as a “flag” & is optional—
  but if omitted assumes TRUE
   – FALSE—does not assume column/row being searched is already
     sorted, requires an exact match
   – TRUE—requires column/row being searched is already sorted in
     ascending order, & that you will accept the largest value <= target
                   VLookup Example
     Scores   USA ROM AUS CAN GER JAP Final
     Maria        8 8.8   9 8.8   7   6 8.15
     Sue        10    9 9.1 8.9   7   9     9
     Yvonne     9.6   9 7.8   5   8 8.5 8.325
     Sonia      7.7 10    9 8.9 9.1 10 9.25




What formula can we enter in E15 so
that when we enter a name in D15, E15
will display that contestant’s score from
the Canadian judge?
                   VLookup Example
     Scores   USA ROM AUS CAN GER JAP Final
     Maria        8 8.8   9 8.8   7   6 8.15
     Sue        10    9 9.1 8.9   7   9     9
     Yvonne     9.6   9 7.8   5   8 8.5 8.325
     Sonia      7.7 10    9 8.9 9.1 10 9.25




=vlookup(d15,a8:h11,5,false)
 If we enter “Yvonne” in d15, E15
displays 5
=vlookup(d15,a8:h11,5)
If we enter “Sonia” in d15, E15 displays
8.8—which is wrong! Why?
       Match(target,vector,flag)
• Searches row or column for “target”, returning its relative
  position in the row or column
• Flag can be
   – 1 which means vector is sorted in ascending order, in which
     case returns largest value <= target
   – 0 which means exact match required, vector need not be sorted
   – -1 which means vector is sorted in descending order, in which
     case returns smallest value >=target
                     Match Example
     Scores   USA ROM AUS CAN GER JAP Final
     Maria        8 8.8   9 8.8   7   6 8.15
     Sue        10    9 9.1 8.9   7   9     9
     Yvonne     9.6   9 7.8   5   8 8.5 8.325
     Sonia      7.7 10    9 8.9 9.1 10 9.25




What formula can we enter in E17 so
that it will display the relative row
corresponding to the winner of the
contest?
=match(max(h8:h11),h8:h11,0)
Produces 4
      Index(table,row,column)
• Used to extract a value from a table based on its
  relative row & column position
• Index(b3:d5,2,3) would display value in cell d4
• Index(b3:b5,2) would display value in cell b4
• Index(b3:d3,2) would display value in cell c3
• Can “nest” match inside index to locate a
  position & return a corresponding value
                     Index Example
     Scores   USA ROM AUS CAN GER JAP Final
     Maria        8 8.8   9 8.8   7   6 8.15
     Sue        10    9 9.1 8.9   7   9     9
     Yvonne     9.6   9 7.8   5   8 8.5 8.325
     Sonia      7.7 10    9 8.9 9.1 10 9.25




What formula can we enter in E17 so that it will display the
name of the winner of the contest?
=index(a8:a11, match( max(h8:h11) ,h8:h11,0) )
Produces Sonia