more spreadsheets n.
Download
Skip this Video
Download Presentation
More Spreadsheets

Loading in 2 Seconds...

play fullscreen
1 / 19

More Spreadsheets - PowerPoint PPT Presentation


  • 73 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'More Spreadsheets' - thao


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
more spreadsheets

More Spreadsheets

Range Formulas & Empty Cells

Logical Functions

Lookup Functions

range arguments
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
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?
logical combinations
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
The IF Function
  • IF(logical expression, value_when_true, value_when_false)
  • =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 ?
information functions
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
  • 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
countif1
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
  • 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
sumif1
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
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
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
VLookup Example

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 example1
VLookup Example

=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
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
Match Example

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
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
Index Example

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