Create Advanced Formulas Flashcards
All “IFS” (as opposed to IF) functions are:
For AND conditions
Ex. “Provide total sales for all businesses in the ‘west’ whose sales are more than $100,000” would be an example of a SUMIFS. Both the condition of “west” and “more than $100,000” – written as “>100000” in an argument – would need to be met, and if they are, SUM the sales.
AND returns TRUE
if ALL conditions are met
OR returns True
If ANY conditions is met
Easier to select rows for functions
View –> Split
Date/Time function
To put a date or time together use :
DATE (month, day and year)
TIME (hour, minute, second).
To pull out a component of a date or time, or to serialize:
MONTH, DAY, YEAR, WEEKDAY, HOUR, MINUTE, SECOND.
NETWORKDAYS function
Excludes weekends
WORKDAY function
Excludes weekends and holidays
Query Editor
- Data
- New query
- From file
- Workbook
- Sheet 1
- Load/load to or edit
Load brings in all, edit allows choices.
How to edit what to import from query editor?
Edit –> select column – remove column
Edit –> merge queries/append queries/split column
“link” to query editor
Data –> connections group –> connections button
Vlookup:
- Don’t use named ranges on this exam unless told to do so (must use F4)
- Don’t include titles in table arrow
3. Named range: Ctrl + F3
4. True uses lowest # in rnage
Hlookup:
Same as VLookup but horizontal
Payment function
- Remember to divide APR by 12 if there are 12 monthly payments
Length of Loan in Years * 12 if there are monthly payments - FV - cash balance after payment is made
- Type - beginning of the month, type is = to 1
Index match
- Uses vlookup and hlookup functions simultaneously
always click on the array (tested) - Array = data
Match function
What specific range are you looking for
Use 0 for exact match
Match is dynamic; do not hard code so you can autofill
Fill down
Control + D