CS 105

MP6: Excel w/ Olympic Data

Due: Tomorrow, April 7 (8:00pm)
On the course website

Arithmetic Functions

SUM, AVERAGE, COUNT

SUMIFS, AVERAGEIFS, COUNTIFS

MAX, MIN

Lookup Functions

INDEX, MATCH

VLOOKUP

Utility Functions

RAND

Linear Search: Check each element for a match; data does not need to be sorted, very slow.
Binary Search: Check the middle element of sorted data; eliminates half the data every time, very fast.

The fourth argument in the VLOOKUP function and the third argument in the MATCH function specifies how the searches should be performed.

A "less than" search finds an exact match or (if no exact match is found) the match closest match that is less than the lookup value.

Lookup Value: 12
Data: [3, 5, 11, 15, 18, 21, 26]

Result (Less Than Match): 11

Data must be sorted in ascending order (increasing) to do a "less than" search.
Use TRUE as the last (fourth) argument to VLOOKUP to do a "less than" search.
Use 1 as the last (third) argument to MATCH to do a "less than" search.

A "less than" search finds an exact match or (if no exact match is found) the match closest match that is greater than the lookup value.

Lookup Value: 12
Data: [26, 21, 18, 15, 11, 5, 3]

Result (Greater Than Match): 15

Data must be sorted in descending order (decreasing) to do a "less than" search.
Use -1 as the last (third) argument to MATCH to do a "less than" search.
Unable to be done with VLOOKUP.

We know binary search looks at the middle element and then discards half the data.

Download the Spreadsheet -- we will use it to show how Excel uses a binary search!