Computing Lecture 3

SPREADSHEETS III
Text
https://www.xkcd.com/1667/
1
AI News
https://www.aiin.healthcare/topics/business-intelligence/mayo-partnering-google
2
Kinds of Learning:
Reinforcement
3
What functions did we
learn last time?
4
Regex Golf:
abba answer
^(.(?!(ll|ss|mm|rr|tt|ff|cc|bb)))*$|^n|ef
5
Search Functions
MATCH
– Look for a value, return its index.
MATCH(value_to_find, range, how_to_match)
– e.g. MATCH(30, A2:A50, 0) look for 30 in A2:A50

– how_to_match = 0, find first value exactly equal.
– how_to_match = 1, find largest value <= value_to_find,
data must be ascending
– how_to_match = -1, find smallest value >= value_to_find,
data must be descending
Order
• Ascending
…, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE
• Descending
TRUE, FALSE, Z-A, …, 2, 1, 0, -1, -2, …
The MATCH function searches for a specified
item in a range of cells, and then returns the
relative position of that item in the range. For
example, if the range A1:A3 contains the values
5, 25, and 38, then the formula
• =MATCH(25,A1:A3,0)
• returns the number 2, because 25 is the
second item in the range.
MATCH(value_to_find, range, how_to_match)
=MATCH(“Hamburger”, A2:A15, 0)
Why 6 and not 7?
9
The INDEX formula takes in a range of values
and a row and column number and returns the
value n the range at that location.
• INDEX(range, row_num, [column_num])

– Select the desired element from a range of cells
– E.g. INDEX(a2:d4, 2, 3)
INDEX(range, row_num, [column_num])
=INDEX(A1:B15, 3, 2)
INDEX(A1:B15,2,2) = ???
11
Match vs. Index
Use Match when you know the value you are
looking for, use Index when you know the
position/location
12
The OFFSET formula returns a reference to a
range that is a specified number of rows and
columns from a cell or range of cells. The
reference that is returned can be a single cell or
a range of cells. You can specify the number of
rows and the number of columns to be
returned.
OFFSET(ref, rows, cols, [height], [width])
– E.g. SUM(OFFSET(C2,-1,-2,3,3))
sums the range A1:C3.
OFFSET(ref, rows, cols, [height], [width])
=OFFSET(A1, 3, 0) =OFFSET(B3,4,0)
14
LARGE AND SMALL
SMALL : returns the kth smallest value in a data
set. Use this function to return values with a
particular relative standing in a data set.
LARGE: returns the kth largest value in a data

set. Use this function to return values with a
particular relative standing in a data set.
=SMALL(array, k) =LARGE(array,k)
Additional Notes About SMALL
• If array is empty, SMALL returns the #NUM!
error value.
• If k ≤ 0 or if k exceeds the number of data
points, SMALL returns the #NUM! error value.
• If n is the number of data points in array,
SMALL(array,1) equals the smallest value, and
SMALL(array,n) equals the largest value.
16
SMALL(array, k) LARGE(array,k)
=SMALL(B2:B15, 3) =LARGE(B11:B15,2)
Does small or large require

the list to be sorted?
17
Reference Functions
• COLUMN(), COLUMN(A5)
ROW(), ROW(A5)
– Column and row of a cell.
• COLUMNS, ROWS
– Number of columns and rows
Search Functions
• LOOKUP
LOOKUP(value, look_in_these, return_from_these)
LOOKUP(5.0, A2:A10, B2:B10)
looks for 5.0 (or next smaller value) in A2:A10
and returns corresponding value in B2:B10.
• Data must be sorted in ascending order

Search Functions
• VLOOKUP
– Like LOOKUP, but works with many columns
– VLOOKUP( lookup_value, table_array,
column_index_num, Approximate_Match(1) or Exact
Match(0) )
– Value you want to look up must be in the first column

of the range of cells you specify
– Column_index_num tells it how many columns over to
look
VLOOKUP Example: Concessions
22
Search Functions
• HLOOKUP
– Like VLOOKUP, but works with many rows.
– HLOOKUP(lookup_value, table_array,
row_index_num, Approximate_Match(1) or Exact
Match(0) )
– Value still must be in the first row.
Search Functions & Best Practice
• Briefly consider ‘Code in the Wild’
• Often, an existing spreadsheet that provides a needed function for

a business or organization will already exist.
• Lookup, Vlookup and Hlookup all do similar things. There’s no

predicting which one was used, so knowing all of them can be
helpful.
• Despite all three of these functions existing, it is common to

combine Index + Match together. Surprisingly, it works faster than
these functions do.
– https://exceljet.net/index-and-match
Index Match Video
https://www.youtube.com/watch?v=F264FpBDX28
Conditional Formatting
• Useful for highlighting values

and trends
Charts
Pivot Tables
• Concessions
– What if you wanted to know the total counts of each item
type you sold?
– Wanted to know revenue totals by item?
These questions are similar to aggregate or group by

Pivot Tables
• Collect, organize and summarize data.
• Allows extraction of information from large tables
without using formulas.
Pivot Table Steps to determine total
revenue by item
• Select the data you want to manipulate (A1:F200)
• Insert Tab, press PivotTable button
– Select for Excel to create a new sheet with a PT
• Pivot Builder will show up
– Builder allows you to take the columns from the original
selected data and use them as report filters, column and
row labels for grouping or values.
– A report filter is similar to filtering (allows you to select
subset of data)
Pivot Table Steps
• Drag the Item Tile in the PT builder into the Rows
section
• Drag the Price tile into the Values Section
• Click on Value Field Settings to change what it
shown.
• If you want to break the sums by category, drag the
category tile into the Columns section of the builder.
Example (from Wikipedia)
Pivot Table Video
• From Microsoft
https://www.youtube.com/watch?v=m0wI61ahfLc
Bookkeeping
Quiz
• First quiz is next week (Oct 3rd)
• In-class, worth 10% of final grade
• On OWL, open book, questions will be applied

rather than repeating knowledge
• Example questions are on OWL

Assignment 1
• First assignment is due October 13th by 11:59pm
• Assignment instructions are up on OWL under

the ‘Assignments’ tab
• Doing the assignment early is excellent studying

for the Quiz
• Assignments are to be done independently

– But numerical answers can be compared
Project
• PDF with instructions is up under ‘Project’ folder on
OWL
• Can be done in groups of 3-4

– Groups must be finalized for the proposal.
• Worth 40% of final grade

– Proposal is 10% of the Project
• Proposal is due Oct 22nd
• Early proposals will get earlier feedback.
– Report is 60% of the Project
– Presentation is 30% of the Project

Computing Lecture 3

Uploaded by

Copyright:

Available Formats

Computing Lecture 3

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Computing Lecture 3

Uploaded by

Copyright:

Available Formats

SPREADSHEETS III

– e.g. MATCH(30, A2:A50, 0) look for 30 in A2:A50

Why 6 and not 7?

• INDEX(range, row_num, [column_num])

LARGE: returns the kth largest value in a data

Does small or large require

• Data must be sorted in ascending order

– Value you want to look up must be in the first column

• Often, an existing spreadsheet that provides a needed function for

• Lookup, Vlookup and Hlookup all do similar things. There’s no

• Despite all three of these functions existing, it is common to

• Useful for highlighting values

These questions are similar to aggregate or group by

• In-class, worth 10% of final grade

• On OWL, open book, questions will be applied

• Example questions are on OWL

• Assignment instructions are up on OWL under

• Doing the assignment early is excellent studying

• Assignments are to be done independently

• Can be done in groups of 3-4

• Worth 40% of final grade

You might also like