Spreadsheets and Data
- Midterm is in two weeks in class. Tyler will email students who need an alternate exam in the next couple of days; contact him if you haven't heard from him.
- We don't maintain the course readings
Plan for Today
- Now, we will apply many programming concepts to spreadsheets, using computers to manipulate text-based data.
- Using Google Sheets
- All our in-class spreadsheets can be viewed here in a Google Drive.
- Note: to edit files, you can make a copy through file on the top menu
Spreadsheets: An Introduction
- Used to store textual information (as opposed to images)
- Links information together in rows and columns
Variables in Spreadsheets
- There are three "types" of variables
- Text (words): left-justified
- "Boolean" values (true and false): center-justified
- Numbers (percents, decimals, and monetary amounts): right-justified
- Experiment: are the numbers always perfectly accurate? What happens if you type a really big number and subtract it from a number one bigger than it?
Data Layout in Spreadsheets
- Rows = objects (samples in data)
- Each row has multiple pieces of information
- Column = fields
- "properties" of the row
- Labelled with a header (should freeze the headers)
- Way of manipulating data
- Almost always used on columns
- Exercise: How many appearances of comic book characters have there been?
Functions behind the scenes
- Look at every row of data
- Perform the calculation on that row of data
- Just like a for loop!
IF(test, true case, false case)
- Automatically includes an if and an else
- Note: use
= instead of
- Logical operators (and/or) still work, but written as
- Exercise: Let's make a new column that is true if the character is female and introduced before 2000.
MINIFS(range, criteria_range, criteria)
MAXIFS(range, criteria_range, criteria)
- How many characters have a non-empty GSM column?
- What is the average number of appearances for a male comic book character? What about a female character? What about any character whose GSM column is not empty?
- Are male characters more likely to be heroes or villains? What about female characters?
- What is the most popular ID?
- Which alignment of character is most likely to die?
- What else can you find out?