Prepared for a presentation in Math for America
on Dec. 2nd, 2021 - 5:30 - 7:30 p.m
Each Google Sheets document is set to read-only. Upon opening a file, please click "File" -> "Make a Copy" to save the file on your own Google Drive and enable editing.
1- All About Color (Google Sheets) (expired)
1- All About Color - Solutions (expired)
Cell Reference
Absolute references
Conditional formatting
Conditional formatting using custom formula
Homework Calendar
Periodic table
Data Validation
2-Sumif and Countif Functions (Google Sheets) (expired)
2-Sumif and Countif Functions - Solutions (expired)
Calculate sum and count according to conditions
Syntax References:
SUMIF(range, criterion, [sum_range])
range: range to be tested
criterion: test condition
sum_range: corresponding range to be summed. (optional, if different from range)
COUNTIF(range, criterion)
range: range to be tested
criterion: test condition
3-Randomness (Google Sheets) (expired)
3-Randomness - Solutions (expired)
Random Selection
Named Ranges
Random questions and solutions generation
Random exit ticket questions
Syntax References:
RANDBETWEEN(low, high)
low: lower bound, inclusive.
high: upper bound, inclusive.
SUBSTITUTE(text_to_search, search_for, replace_with)
4-Web Scraping (Google Sheets) (expired)
4-Web Scraping - Solutions (expired)
Web Scraping
CSV Data Extraction
Price Check
Syntax References:
IMPORTHTML(url, query, index)
url: web page address.
query: "list" or "table" data structure type.
index: which list or table, starts from 1.
IMPORTDATA(url)
url: .csv or .tsv file .
IMPORTXML(url, xpath_query)
url: web page address.
xpath_query: element XPath
5-Data Organization and Analysis (Google Sheets) (expired)
5-Data Organization and Analysis - Solutions (expired)
Analyze NYC DOE High Schools
Syntax References:
UNIQUE(range)
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
TRANSPOSE(array_or_range)
FILTER(range, condition1, [condition2, ...])
range: range of data to be filtered
condition: condition for data to be selected.
QUERY(data, query, [headers])
data: range of data
query: command similar to SQL
headers: the number of header rows; set to 0 to eliminate header
Common SQL keywords
Select
Where
Not, And, Or
Contains
Order By [ASC|DESC]
Google Query Language Syntax: https://developers.google.com/chart/interactive/docs/querylanguage#Language_Syntax