Beginner Flashcards
MINUS(Date 1, Date 2)
Function which returns the difference between 2 dates. Output can be negative OR positive.
DATEDIF(Date 1, Date2, “D” or “M”)
Function which returns the # of whole days (“D”) or months (“M”) between 2 dates. (Note: Date 1 < Date 2)
NETWORKDAYS(Date 1, Date 2)
Function which returns the # of working days between 2 dates.
WEEKDAY(Date, Type)
Function which returns the day of the week as a number depending on ‘Type’ parameter.
What are the 3 Types in WEEKDAY(Date, Type)?
Type 1: Starts from Sunday = 1
Type 2: Starts from Monday = 1
Type 3: Starts from Monday = 0
TEXT(Date, “ddd” or “mmm”)
Function which returns the day of the week or the month of the year as 3 letters.
Difference between “mmm”, “mmmm”, “mmmmm”?
“mmm” = 3 letters
“mmmm” = entire word
“mmmmm” = 1st letter
(Note: “ddddd” = “dddd”)
What is a range?
A collection of cells.
Defined by the upper left cell and lower right cell, separated by a colon.
What is a named range? Why is it useful?
A range that has a name assigned to it. You can use that name in place of normal cell references.
They make formulas easier to understand (and debug), simplify complicated spreadsheets, and simplify macros.
What is a protected range?
A range that can only be edited by some users.
What is Data Validation?
Feature used to control what a user can enter into a cell.
How to highlight duplicate entries in a column?
Use conditional formatting:
- Select column (A) > Conditional Formatting
- Select Custom Formula from drop-down list
- Enter =countif(A:A, A1) > 1
- Edit formatting styles
How can you see all conditional formatting rules on your spreadsheet?
Select entire spreadsheet, command+A, or shift+command+space.
SPARKLINE(Range)
Function which lets you see the trend of a range of numbers as a mini-chart in a cell.
How to freeze header row?
View > Freeze > Choose # of rows/columns
OR
Drag the grey line at the top left corner
MAX(Range)
Function which returns the maximum value from a range.
MIN(Range)
Function which returns the minimum value from a range.
SMALL(Range, n)
Function which returns the nth smallest number from a range.
LARGE(Range, n)
Function which returns the nth largest number from a range.
COUNTIF(Range, criterion)
Function which returns the conditional count from a range, depending on criterion (ex: “>20”).
IF statements
IF(logical_expression, do_this_if_True, do_this_if_False).
Note: do_this_if_False is optional
SUM(Range)
Function which returns the sum of a range.
Relative Reference
A cell without the $ sign before the row and column coordinates. When a formula with relative cell references are copied to another cell, the reference changes based on a relative position of rows and columns.
Absolute Reference
A cell with $ sign before the row and column coordinates. When a formula with absolute cell references are copied to another cell, the reference does not change.
How to toggle Absolute/Relative References?
fn + F4
Difference between Equal Rank and Average Rank?
Equal rank (RANK.EQ) returns the top rank of a value in a given range, while Average rank (RANK.AVG) returns the average rank.
AND Operator
AND(logical_exp_1, logical_exp_2, logical_exp_3, …)
The function will return TRUE if all expressions are true. It will return FALSE otherwise.
What does AND(0, 1, 2) return?
FALSE. Google Sheets treats 0 as false.
How to create a simple chart?
- Select your columns (can include headers).
- Insert > Chart
- Edit Chart (type, add series, title, axis, etc.)
How to add a trendline on a chart?
Chart Editor > Customize > Series > Trendline
FILTER(Range, Condition 1)
Function which returns all the values in a range which follow a certain condition.
- How to return all the dates in August from a group of dates?
- How to return the # of student absences for each of those days in August?
- =FILTER(Dates, MONTH(Dates) = 8)
2. =FILTER(Absences, MONTH(Dates) = 8)
What does filtering do?
Filtering refers to displaying only rows or values that meet a specific criteria.
How to use filters?
Select the range you want to filter and then click filter icon from toolbar.
You can have different filter conditions for different columns.
3 Filter Functionalities
- Filter Feature: Any user can modify these features.
- Filter View: Each user can have their own filter views.
- Filter Functions: Filtering using functions such as FILTER, SORT, etc.
