VIEWS: 0 PAGES: 19

• pg 1
```									 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,
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
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
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

```
To top