🏠 DATA+ PALACE Memory Palace Techniques Big 5 Central Tendency COUNTIF SUMIF IF/IFS Math Tools Wildcards ARRAYFORMULA IMPORTRANGE MAXIFS/MINIFS Review Schedule Cheat Sheet

🧠 The Data+ Memory Palace

Every formula lives in a room. Walk through the house. Never forget a formula again.
Method of Loci + Chunking + Spaced Repetition + Real BSP Data

25
Formulas
10
Rooms
7
Memory Techniques
$6.4M
Real BSP Data
Function Name
Range (where to look)
Criteria (the filter)
Output Range (what to calculate)
Values / Numbers
Syntax Characters

🏠 Robert's Memory Palace

Click any room to jump to its formulas. Close your eyes and walk through this house before the exam.

╔══════════════════════════════════════════════════════════╗ ║ 🏠 ROBERT'S FORMULA HOUSE 🏠 ║ ╠══════════════════════════════════════════════════════════╣ ║ ║ ║ ┌─── UPSTAIRS ────────────────────────────────────┐ ║ ║ │ 🛏️ BEDROOM 🛋️ GUEST ROOM │ ║ ║ │ ARRAYFORMULA IMPORTRANGE │ ║ ║ │ "One alarm wakes "Guest brings data │ ║ ║ │ the whole room" from another house" │ ║ ║ └─────────────────────────────────────────────┘ ║ ║ | ║ ║ STAIRS ║ ║ | ║ ║ ┌─── MAIN FLOOR ─────────────────────────────────┐ ║ ║ │ │ ║ ║ │ 🚪 FRONT DOOR ──▶ 🏆 BIG 5 ROOM │ ║ ║ │ SUM AVG COUNT MAX MIN │ ║ ║ │ "5 trophies on a shelf" │ ║ ║ │ │ ║ ║ │ 🍳 KITCHEN 🛋️ LIVING ROOM │ ║ ║ │ Mean Median Mode COUNTIF / COUNTIFS │ ║ ║ │ "Scale, knife, "Bouncer counting │ ║ ║ │ popular dish" who gets in" │ ║ ║ │ │ ║ ║ │ 🍽️ DINING ROOM 💼 HOME OFFICE │ ║ ║ │ SUMIF SUMIFS IF / IFS │ ║ ║ │ AVERAGEIF(S) "Sorting machine │ ║ ║ │ "Waiter adds up routes papers" │ ║ ║ │ certain items" │ ║ ║ └─────────────────────────────────────────────┘ ║ ║ ║ ║ ┌─── OUTSIDE ─────────────────────────────────────┐ ║ ║ │ 🔧 GARAGE 🌻 BACKYARD │ ║ ║ │ ABS ROUND FLOOR Wildcards * and ? │ ║ ║ │ CEILING MOD "Wildflowers: │ ║ ║ │ "Tools on the wall" * = any, ? = one" │ ║ ║ └─────────────────────────────────────────────┘ ║ ║ ║ ║ ┌─── UNDERGROUND ─────────────────────────────────┐ ║ ║ │ 🏆 BASEMENT │ ║ ║ │ MAXIFS / MINIFS │ ║ ║ │ "Trophy case: biggest & smallest with rules" │ ║ ║ └─────────────────────────────────────────────┘ ║ ╚══════════════════════════════════════════════════════════╝
🚪🏆
Front Door → Big 5 Room
SUM, AVERAGE, COUNT, MAX, MIN -- the 5 trophies on a shelf you see when you walk in
🧠 Trigger: "5 shiny trophies on the entry shelf"
 🍳⚖️
Kitchen
Mean, Median, Mode -- the kitchen scale (AVERAGE), knife cutting in the MIDDLE, the most popular dish
🧠 Trigger: "Scale, knife, popular dish"
 🛋️🚧
Living Room
COUNTIF / COUNTIFS -- a bouncer at the door counting who gets in based on the rules
🧠 Trigger: "Bouncer with a clipboard"
 🍽️🧾
Dining Room
SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS -- the waiter adding up only certain items on the bill
🧠 Trigger: "Waiter tallying select items"
 💼📂
Home Office
IF / IFS -- the sorting machine on the desk that routes papers into folders
🧠 Trigger: "Sorting machine on the desk"
 🔧🛠️
Garage
ABS, ROUND, FLOOR, CEILING, MOD -- tools hanging on the wall
🧠 Trigger: "Tape measure, level, floor jack, ceiling fan"
 🌻🌿
Backyard
Wildcards * and ? -- wildflowers in the yard. * = any flower. ? = exactly one flower.
🧠 Trigger: "Wildflowers -- star patch and single bloom"
 🛏️⏰
Upstairs Bedroom
ARRAYFORMULA -- one alarm clock wakes up the ENTIRE room at once
🧠 Trigger: "One alarm, everyone jumps"
 🛋️📦
Guest Room
IMPORTRANGE -- a guest arriving with a suitcase of data from another house
🧠 Trigger: "Guest carrying a suitcase from another spreadsheet"
 🏆🔒
Basement
MAXIFS / MINIFS -- the trophy case: biggest and smallest awards, but with conditions
🧠 Trigger: "Locked trophy case -- only winners who meet the criteria"

🧠 7 Memorization Weapons

These are the proven techniques woven into every section of this guide.

01
🏠 Memory Palace (Method of Loci)
Assign each formula group to a room in your house. Walk through the house mentally. Each room triggers the formulas. Used by memory champions since ancient Greece.
"I walk in the front door and see 5 trophies = SUM, AVG, COUNT, MAX, MIN"
02
📦 Chunking
Group related formulas into families. Your brain processes 7 +/- 2 chunks. Instead of 25 formulas, you remember 10 rooms with 2-4 formulas each.
Big 5 chunk | Conditional chunk | Math Tools chunk
03
🔁 Spaced Repetition
Review at increasing intervals: Day 1, Day 3, Day 7, Day 14, Day 30. The forgetting curve is beaten by timed reviews, not cramming.
See the Review Schedule section below
04
🔤 Mnemonics & Acronyms
Create memorable phrases. The sillier, the stickier. "SACMM" = Sum Average Count Max Min. "WWA" = Where What Add (SUMIF order).
"Sam Ate Cake, Maxed Milkshakes" = SACMM
05
🎭 Analogies
Every formula is linked to a real-world scenario. COUNTIF = bouncer. SUMIF = waiter. IF = sorting machine. Concrete images stick better than abstract syntax.
"SUMIF is the waiter only adding up YOUR items on the bill"
06
❓ Active Recall
Each section ends with a quiz question. Cover the answer and try to recall it. Retrieval practice is 50% more effective than re-reading.
Every formula section has a "Quick Recall" quiz
07
🎨 Visual Encoding
Color-coded formula parts throughout. Green = range, Yellow = criteria, Blue = function, Purple = output range. Your visual cortex does the heavy lifting.
See the color legend at the top of every breakdown

🚪🏆 Room 1: Front Door → The Big 5

You walk through the front door and see 5 trophies on a shelf. Each trophy is engraved with a function name.

🔤 Mnemonic: SACMM = "Sam Ate Cake, Maxed Milkshakes"
SUM • AVERAGE • COUNT • MAX • MIN
🏆 Trophy 1

=SUM( ) -- Add Everything Up

=SUM(G2:G21)
^ ^^^ ^^^^^^^
| | |
| | +-- 📗 The RANGE of cells to add (column G, rows 2-21)
| +-- 🔵 The FUNCTION name: "add these up"
+-- ⚡ Equals sign = "I'm a formula!"
📢 Plain English: "Go through every cell from G2 to G21 and add all the numbers together. Give me the total."
🗄️ SQL Equivalent
SELECT SUM(revenue) FROM jobs;
🔧 BSP Real Data
=SUM(revenue) across all completed jobs = $6,456,070
That's 10,509 completed jobs worth of plumbing revenue. The SUM trophy is the biggest one on the shelf.
⚠️
Exam Tip: SUM ignores text and blanks. If a cell has "N/A" text, SUM skips it. But if it has an #N/A error, SUM returns an error. Use SUMIF to filter, or wrap in IFERROR.
❓ Quick Recall: What does =SUM(A1:A100) do if A50 contains the text "pending"?
It ignores "pending" and sums the other 99 numeric cells. Text is skipped by SUM.
🏆 Trophy 2

=AVERAGE( ) -- The Mean

=AVERAGE(G2:G21)
^ ^^^^^^^ ^^^^^^^
| | |
| | +-- 📗 The RANGE to average
| +-- 🔵 AVERAGE = sum / count (the mean)
+-- ⚡ Formula start
📢 Plain English: "Add up every number from G2 to G21, then divide by how many numbers there are. That's the average."
🗄️ SQL Equivalent
SELECT AVG(total) FROM jobs WHERE job_status = 'completed';
🔧 BSP Real Data
=AVERAGE(job_total) = $2,120 average ticket
But wait -- compare this to the MEDIAN ($626). The gap of $1,494 means outliers are pulling the average UP. A few $20K+ jobs skew everything. This is why Data+ tests you on mean vs. median.
⚠️
Exam Tip: AVERAGE only counts cells with numbers. Blank cells are NOT counted in the denominator. If 10 cells have values and 5 are blank, AVERAGE divides by 10, not 15.
❓ Quick Recall: If AVERAGE = $2,120 and MEDIAN = $626, what does that tell you about the data distribution?
The data is RIGHT-SKEWED (positively skewed). A few very large values (outliers) pull the mean far above the median. Mean > Median = right skew.
🏆 Trophy 3

=COUNT( ) -- How Many Numbers?

=COUNT(G2:G21)
^ ^^^^^ ^^^^^^^
| | |
| | +-- 📗 The RANGE to count
| +-- 🔵 COUNT = how many cells have NUMBERS (not text!)
+-- ⚡ Formula start
📢 Plain English: "Go through G2 to G21 and count how many cells contain a number. Skip blanks and text."
🗄️ SQL Equivalent
SELECT COUNT(total) FROM jobs WHERE job_status = 'completed';
🔧 BSP Real Data
=COUNT(completed_jobs) = 10,509 completed jobs
That's how many jobs BSP has completed with revenue attached. COUNT only counts numeric cells -- if a job total is blank, it's not counted.
⚠️
Exam Tip: COUNT counts NUMBERS only. COUNTA counts non-blank cells (text + numbers). COUNTBLANK counts empty cells. Know all three.
❓ Quick Recall: What's the difference between COUNT, COUNTA, and COUNTBLANK?
COUNT = only numbers. COUNTA = any non-empty cell (text, numbers, dates). COUNTBLANK = empty cells only.
🏆 Trophy 4

=MAX( ) -- The Biggest Number

=MAX(G2:G21)
^ ^^^ ^^^^^^^
| | |
| | +-- 📗 The RANGE to search
| +-- 🔵 MAX = find the LARGEST value
+-- ⚡ Formula start
📢 Plain English: "Scan every number from G2 to G21 and tell me which one is the biggest."
🗄️ SQL Equivalent
SELECT MAX(total) FROM jobs WHERE job_status = 'completed';
🔧 BSP Real Data
=MAX(job_total) = $41,356 (Job #3164-1)
The biggest single job BSP has completed. That's one expensive plumbing project -- likely a full sewer replacement or major renovation.
❓ Quick Recall: Does MAX work on dates?
Yes! Dates are stored as numbers. MAX on a date range returns the most RECENT date. MIN returns the oldest.
🏆 Trophy 5

=MIN( ) -- The Smallest Number

=MIN(G2:G21)
^ ^^^ ^^^^^^^
| | |
| | +-- 📗 The RANGE to search
| +-- 🔵 MIN = find the SMALLEST value
+-- ⚡ Formula start
📢 Plain English: "Scan every number from G2 to G21 and tell me which one is the smallest."
🔧 BSP Real Data
=MIN(job_total) = $24
The smallest completed job. Probably a dispatch fee or minor diagnostic. The RANGE between MIN ($24) and MAX ($41,356) = $41,332 -- that's massive variance.
❓ Quick Recall: What's the range of BSP job totals? (Range = MAX - MIN)
$41,356 - $24 = $41,332. A huge range indicates high variability in job sizes.

🍳 Room 2: Kitchen -- Measures of Central Tendency

You walk into the kitchen and see three things: a kitchen scale (AVERAGE/Mean), a knife cutting exactly in the middle (MEDIAN), and the most popular dish on the menu board (MODE).

🔤 Mnemonic: "The Kitchen MMM"
Mean (the scale weighing everything) • Median (the knife in the middle) • Mode (the most popular dish)
🔪 The Knife

=MEDIAN( ) -- The Middle Value

=MEDIAN(G2:G21)
^ ^^^^^^ ^^^^^^^
| | |
| | +-- 📗 The RANGE to find the middle of
| +-- 🔵 MEDIAN = sort all values, pick the MIDDLE one
+-- ⚡ Formula start
📢 Plain English: "Line up all the numbers from smallest to largest. Point to the one in the exact middle. That's the median. If there's an even count, average the two middle values."
🗄️ SQL Equivalent
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) FROM jobs;
🔧 BSP Real Data
=MEDIAN(job_total) = $626 -- the TRUE typical ticket
Compare to AVERAGE ($2,120). The $1,494 gap = OUTLIERS ARE PRESENT.
📊 Mean > Median = RIGHT SKEWED distribution (a few big jobs pull the average up)
⚠️
Exam Tip: The exam LOVES asking when to use mean vs. median. Rule: If outliers exist, use MEDIAN. It's resistant to extreme values. Mean gets pulled by outliers. BSP proves it: $2,120 avg vs $626 median. The median is the truth.
❓ Quick Recall: BSP Average = $2,120, Median = $626. Is this left-skewed or right-skewed?
RIGHT-SKEWED (positively skewed). Mean > Median always = right skew. The tail stretches to the right (toward high-value jobs).
🍝 Popular Dish

=MODE( ) -- The Most Frequent Value

=MODE(G2:G21)
^ ^^^^ ^^^^^^^
| | |
| | +-- 📗 The RANGE to check frequency
| +-- 🔵 MODE = the value that appears MOST OFTEN
+-- ⚡ Formula start
📢 Plain English: "Look at every number. Which number shows up the most times? That's the mode. It's the most popular dish on the menu -- everyone orders it."
🗄️ SQL Equivalent
SELECT total, COUNT(*) as freq FROM jobs GROUP BY total ORDER BY freq DESC LIMIT 1;
🔧 BSP Real Data
The MODE of BSP job totals is likely near the $89 dispatch fee range -- the most common charge. When lots of jobs are diagnostics at a flat fee, that amount repeats the most.
📊 This means BSP has a multimodal distribution: one peak at dispatch fees, another at standard repairs.
⚠️
Exam Tip: MODE returns #N/A if no value repeats. MODE.MULT returns multiple modes if there's a tie. A dataset can be unimodal (one mode), bimodal (two modes), or multimodal.
❓ Quick Recall: Data set: [5, 3, 7, 3, 9, 7, 3]. What's the mode?
Mode = 3 (appears 3 times, more than any other value).

🛋️ Room 3: Living Room -- COUNTIF / COUNTIFS

A bouncer stands at the living room door with a clipboard. He counts how many people get in -- but ONLY those who match his rules.

🔤 Mnemonic: "The Bouncer's Clipboard"
COUNTIF = bouncer with ONE rule ("Only people in blue shirts")
COUNTIFS = bouncer with MULTIPLE rules ("Blue shirts AND over 21")
🚧 One Rule

=COUNTIF( ) -- Count With One Condition

=COUNTIF(B2:B21, "Sewer")
^ ^^^^^^^ ^^^^^^^ ^^^^^^^^
| | | |
| | | +-- 🟡 CRITERION: what to look for ("Sewer")
| | +-- 📗 RANGE: where to look (the labels column)
| +-- 🔵 COUNTIF = "count cells that match"
+-- ⚡ Formula start
📢 Plain English: "Go through column B. Every time you see 'Sewer', tick one on your clipboard. Tell me the final count." The bouncer looks at one column and has one rule.
🗄️ SQL Equivalent
SELECT COUNT(*) FROM jobs WHERE job_type = 'Sewer';
🔧 BSP Real Data
=COUNTIF(job_type, "Sewer") = 9 jobs
Only 9 completed sewer jobs out of 10,509 total. That's less than 0.1% of jobs but sewer has the highest average ticket. Huge opportunity.
⚠️
Exam Tip: COUNTIF criteria supports operators: ">100", "<>Sewer" (not equal), ">="&A1 (reference a cell). Wrap operators in quotes!
❓ Quick Recall: =COUNTIF(A1:A100, ">500") -- what does this count?
It counts how many cells in A1:A100 contain a number greater than 500.
🚧🚧 Multiple Rules

=COUNTIFS( ) -- Count With Multiple Conditions

=COUNTIFS(B2:B21, "Sewer", D2:D21, ">5000")
^ ^^^^^^^^ ^^^^^^^ ^^^^^^^^ ^^^^^^^ ^^^^^^^^
| | | | | |
| | | | | +-- 🟡 CRITERIA 2: greater than $5,000
| | | | +-- 📗 RANGE 2: where to check criteria 2
| | | +-- 🟡 CRITERIA 1: must be "Sewer"
| | +-- 📗 RANGE 1: where to check criteria 1
| +-- 🔵 COUNTIFS = "count cells matching ALL conditions" (AND logic)
+-- ⚡ Formula start
📢 Plain English: "The bouncer now has TWO rules on his clipboard. Only count people who are wearing a blue shirt AND are over 21. BOTH conditions must be true. It's AND logic, not OR."
🗄️ SQL Equivalent
SELECT COUNT(*) FROM jobs WHERE job_type = 'Sewer' AND total > 5000;
🔧 BSP Real Data
=COUNTIFS(job_type,"Sewer", total,">5000") = counts sewer jobs over $5K
With an avg sewer ticket of $8,679, most sewer jobs are above $5K. High-value work that warrants dedicated landing pages.
⚠️
Exam Tip: COUNTIFS uses AND logic (all criteria must match). There is NO built-in COUNTIF with OR logic. To do OR, use: =COUNTIF(range,"A") + COUNTIF(range,"B")
❓ Quick Recall: COUNTIFS always uses AND or OR logic?
AND logic. Every criteria pair must be true for a row to be counted. For OR, add separate COUNTIFs together.

🍽️ Room 4: Dining Room -- SUMIF / SUMIFS / AVERAGEIF

A waiter is at the table. He only adds up certain items on the bill -- not everything, just what matches your request.

🔤 CRITICAL Mnemonic: The SUMIF/SUMIFS Flip!
SUMIF arg order: Where, What, Add (range, criterion, sum_range)
SUMIFS arg order: Add, Where, What (sum_range FIRST, then range/criterion pairs)

🧠 Remember: "SUMIF = Where What Add" (WWA)
🧠 Remember: "SUMIFS = Add Where What" (AWW) -- "Aww, it's backwards!"

⚠️ THE FLIP IS THE #1 EXAM TRAP. SUMIFS puts the sum range FIRST.
🧾 One Filter

=SUMIF( ) -- Sum With One Condition

=SUMIF(B2:B21, "Sewer", G2:G21)
^ ^^^^^ ^^^^^^^ ^^^^^^^^ ^^^^^^^
| | | | |
| | | | +-- 🟣 SUM RANGE: add up numbers FROM HERE
| | | +-- 🟡 CRITERION: looking for this value
| | +-- 📗 RANGE: look IN here (the labels column)
| +-- 🔵 SUMIF = "sum only matching rows"
+-- ⚡ Formula start
📢 Plain English: "Go through column B (job types). Every time you find 'Sewer', grab the number from the SAME ROW in column G (revenue). Add all those matching numbers together."

🍽️ Analogy: "Waiter, I only want to see my appetizer charges. Go through the menu items (range), find 'appetizer' (criterion), and total up their prices (sum_range)."
🗄️ SQL Equivalent
SELECT SUM(total) FROM jobs WHERE job_type = 'Sewer';
🔧 BSP Real Data
=SUMIF(job_type,"Sewer",total) = $26,038 total sewer revenue
9 sewer jobs x avg $8,679 ticket. Sewer is 0.4% of revenue from 0.09% of jobs -- highest per-job value category.
⚠️
Exam Tip: In SUMIF, the sum_range is OPTIONAL. If omitted, it sums the range itself: =SUMIF(A1:A10,">100") sums all values in A1:A10 that are greater than 100.
❓ Quick Recall: What's the argument order for SUMIF?
SUMIF = Where, What, Add (range, criterion, sum_range). Remember: WWA.
🧾🧾 Multiple Filters

=SUMIFS( ) -- Sum With Multiple Conditions

=SUMIFS(G2:G21, B2:B21, "Sewer", D2:D21, ">5000")
^ ^^^^^^ ^^^^^^^ ^^^^^^^ ^^^^^^^^ ^^^^^^^ ^^^^^^^^
| | | | | | |
| | | | | | +-- 🟡 CRITERIA 2
| | | | | +-- 📗 RANGE 2
| | | | +-- 🟡 CRITERIA 1
| | | +-- 📗 RANGE 1: where to check
| | +-- 🟣 SUM RANGE: ⚠️ THIS COMES FIRST in SUMIFS!
| +-- 🔵 SUMIFS = "sum matching ALL conditions"
+-- ⚡ Formula start
📢 Plain English: "Add up revenue (G column) but ONLY for rows where column B = 'Sewer' AND column D > $5,000."

⚠️ THE FLIP: Notice the sum_range (G2:G21) is the FIRST argument now! In SUMIF it was LAST. In SUMIFS it's FIRST. "Aww, it's backwards!"
🗄️ SQL Equivalent
SELECT SUM(total) FROM jobs WHERE job_type = 'Sewer' AND total > 5000;
⚠️
Exam Tip: The SUMIF-to-SUMIFS argument flip is one of the most common exam tricks. SUMIF = range, criterion, sum_range. SUMIFS = sum_range, range1, criterion1, range2, criterion2. The S (plural) moves sum_range to the front.
❓ Quick Recall: In SUMIFS, where does the sum_range go? First or last?
FIRST! SUMIFS = Add, Where, What (AWW -- "Aww, it's backwards!"). The sum_range comes before the criteria pairs.
🧾 Average Bill

=AVERAGEIF( ) -- Average With One Condition

=AVERAGEIF(B2:B21, "Sewer", G2:G21)
^ ^^^^^^^^^ ^^^^^^^ ^^^^^^^^ ^^^^^^^
| | | | |
| | | | +-- 🟣 AVERAGE RANGE: average these numbers
| | | +-- 🟡 CRITERION: what to match
| | +-- 📗 RANGE: where to look
| +-- 🔵 AVERAGEIF = "average only matching rows"
+-- ⚡ Formula start
📢 Plain English: "Find all sewer jobs in column B, grab their revenue from column G, and calculate the average of JUST those values."

Same argument order as SUMIF: Where, What, Add (WWA).
🗄️ SQL Equivalent
SELECT AVG(total) FROM jobs WHERE job_type = 'Sewer';
🔧 BSP Real Data
=AVERAGEIF(job_type,"Sewer",total) = $8,679 avg sewer ticket
Compare to overall average of $2,120. Sewer jobs are 4.1x the average ticket. Every sewer lead is worth 4 regular leads.
❓ Quick Recall: Does AVERAGEIF follow SUMIF order (WWA) or SUMIFS order (AWW)?
SUMIF order! AVERAGEIF = Where, What, Add (WWA). Only SUMIFS/AVERAGEIFS/MAXIFS/MINIFS put the output range first.
🧾🧾 Filtered Average

=AVERAGEIFS( ) -- Average With Multiple Conditions

=AVERAGEIFS(G2:G21, B2:B21, "Sewer", E2:E21, "completed")
^ ^^^^^^^^^^ ^^^^^^^ ^^^^^^^ ^^^^^^^^ ^^^^^^^ ^^^^^^^^^^^
| | | | | | |
| | | | | | +-- 🟡 CRITERIA 2
| | | | | +-- 📗 RANGE 2
| | | | +-- 🟡 CRITERIA 1
| | | +-- 📗 RANGE 1
| | +-- 🟣 AVERAGE RANGE: ⚠️ FIRST (same flip as SUMIFS!)
| +-- 🔵 AVERAGEIFS = "average matching ALL conditions"
+-- ⚡ Formula start
📢 Plain English: "Average the revenue column, but ONLY for rows where job type = 'Sewer' AND status = 'completed'."

⚠️ Same flip as SUMIFS: output range comes FIRST. AWW pattern.
⚠️
Exam Tip: All the "S" versions (SUMIFS, AVERAGEIFS, MAXIFS, MINIFS) put the output range FIRST. The non-S versions (SUMIF, AVERAGEIF) put the output range LAST. The S flips it.
❓ Quick Recall: What do ALL the "S" formulas (SUMIFS, AVERAGEIFS, MAXIFS, MINIFS) have in common regarding argument order?
They ALL put the output/calculation range FIRST, followed by criteria range/value pairs. The "S" = "Start with the sum/average/max/min range."

💼 Room 5: Home Office -- IF / IFS

On the desk sits a sorting machine. Papers go in the top. The machine checks each paper and routes it into different folders based on rules.

🔤 Mnemonic: "If This Then That"
IF = one question, two folders (true or false)
IFS = multiple questions, multiple folders (first true wins)
📂 One Question

=IF( ) -- The Decision Gate

=IF(G2 > 1000, "High Value", "Standard")
^ ^^ ^^^^^^^^^^ ^^^^^^^^^^^^ ^^^^^^^^^^
| | | | |
| | | | +-- 🟠 FALSE VALUE: output if condition is FALSE
| | | +-- 📗 TRUE VALUE: output if condition is TRUE
| | +-- 🟡 CONDITION: the yes/no question
| +-- 🔵 IF = "test a condition, return one of two values"
+-- ⚡ Formula start
📢 Plain English: "Look at cell G2. Is the number greater than 1000? If YES, write 'High Value'. If NO, write 'Standard'."

🔧 Analogy: The sorting machine reads each paper. One question: "Is this over $1000?" If yes, drop it in the "High Value" folder. If no, drop it in "Standard."
🗄️ SQL Equivalent
SELECT CASE WHEN total > 1000 THEN 'High Value' ELSE 'Standard' END FROM jobs;
🔧 BSP Real Data
=IF(total > 2120, "Above Average", "Below Average")
Using BSP's $2,120 average ticket as the threshold. Since MEDIAN is $626, most jobs would be labeled "Below Average" -- another proof that mean is misleading with skewed data.
⚠️
Exam Tip: You can NEST IF statements: =IF(A1>90,"A",IF(A1>80,"B","C")). But nested IFs get messy fast. For 3+ conditions, use IFS instead.
❓ Quick Recall: =IF(10>5, "Yes", "No") -- what does this return?
"Yes" -- because 10 > 5 is TRUE, so it returns the true_value.
📂📂📂 Multiple Questions

=IFS( ) -- Multiple Decision Gates

=IFS(G2>10000,"Premium", G2>2000,"High", G2>500,"Mid", TRUE,"Low")
^ ^^^ ^^^^^^^^^ ^^^^^^^^^ ^^^^^^^^ ^^^^^^ ^^^^^^^ ^^^^^ ^^^^ ^^^^^
| | | | | | | | | |
| | | | | | | | | +-- 🟠 DEFAULT value
| | | | | | | | +-- 🟡 TRUE = "catch all"
| | | | | | | +-- 📗 Result 3
| | | | | | +-- 🟡 Test 3
| | | | | +-- 📗 Result 2
| | | | +-- 🟡 Test 2
| | | +-- 📗 Result 1
| | +-- 🟡 Test 1 (checked FIRST)
| +-- 🔵 IFS = "check tests in order, first TRUE wins"
+-- ⚡ Formula start
📢 Plain English: "Check the tests in ORDER. First one that's TRUE wins. Is it over $10K? Premium. Over $2K? High. Over $500? Mid. None of those? Low."

⚠️ Order matters! Check the biggest first. If you check >500 before >10000, everything over $500 gets caught first and you never reach the $10K test.
🗄️ SQL Equivalent
SELECT CASE WHEN total>10000 THEN 'Premium' WHEN total>2000 THEN 'High' WHEN total>500 THEN 'Mid' ELSE 'Low' END FROM jobs;
🔧 BSP Real Data
BSP Tech Tiers:
=IFS(ticket>10000,"Elite", ticket>5000,"Premium", ticket>2000,"Standard", TRUE,"Entry")
This creates the tech efficiency tiers from the Ramp analysis data.
⚠️
Exam Tip: Always end IFS with TRUE, "default" as the last pair. This is the "else" / catch-all. Without it, if no test is TRUE, IFS returns #N/A error.
❓ Quick Recall: What happens if NO condition in IFS is true and there's no TRUE catch-all?
#N/A error. Always end IFS with TRUE, "default value" as the catch-all.

🔧 Room 6: Garage -- Math Tools

Walk into the garage. Tools hang on the wall: a tape measure (ABS = distance, always positive), a level (ROUND = precision), a floor jack (FLOOR), a ceiling fan (CEILING), and a wrench with a remainder (MOD).

🔤 Mnemonic: "A Real Fancy Car, Man!" = ABS, ROUND, FLOOR, CEILING, MOD
ABS (tape measure) • ROUND (level) • FLOOR (floor jack) • CEILING (ceiling fan) • MOD (wrench)
📏 Tape Measure

=ABS( ) -- Absolute Value (Always Positive)

=ABS(-47) → returns 47
^ ^^^ ^^^
| | |
| | +-- 🟠 VALUE: any number (negative becomes positive)
| +-- 🔵 ABS = "absolute value" = distance from zero
+-- ⚡ Formula start
📢 Plain English: "How far is this number from zero? Don't care about direction, just distance." A tape measure doesn't give negative measurements. -47 is 47 units from zero.

🔧 Analogy: A tape measure on the garage wall. You pull it out 47 inches. It doesn't matter which direction -- it's 47 inches.
🗄️ SQL Equivalent
SELECT ABS(-47); -- returns 47
🔧 BSP Real Data
=ABS(revenue_this_month - revenue_last_month)
If March revenue was $180K and February was $210K, the difference is -$30K. ABS gives you $30,000 -- the magnitude of change without the sign.
❓ Quick Recall: =ABS(-100) + ABS(100) = ?
200. ABS(-100) = 100, ABS(100) = 100. Both are 100 units from zero.
📐 Level

=ROUND( ) -- Round to Precision

=ROUND(3.14159, 2) → returns 3.14
^ ^^^^^ ^^^^^^^ ^
| | | |
| | | +-- 🟡 PLACES: how many decimal places to keep
| | +-- 🟠 VALUE: the number to round
| +-- 🔵 ROUND = standard rounding (5+ rounds up)
+-- ⚡ Formula start
📢 Plain English: "Take 3.14159 and keep only 2 decimal places. Look at the 3rd decimal (1) -- it's less than 5, so round down. Result: 3.14."
🔧 BSP Real Data
=ROUND(6456070/10509, 2) = $614.35 revenue per job (more precise than $614)
Use ROUND(value, 0) for whole dollars, ROUND(value, 2) for cents.
❓ Quick Recall: =ROUND(2.555, 2) = ?
2.56 (the third decimal is 5, rounds up).
⬆️⬇️ Force Direction

=ROUNDUP( ) / =ROUNDDOWN( )

=ROUNDUP(3.141, 2) → returns 3.15 (always rounds AWAY from zero)
=ROUNDDOWN(3.149, 2) → returns 3.14 (always rounds TOWARD zero)
📢 Plain English: "ROUNDUP = always push the number UP (ceiling direction). ROUNDDOWN = always chop off the extra (truncate). Unlike ROUND, they don't care about the 5-rule -- they always go one direction."
⚠️
Exam Tip: ROUNDUP rounds away from zero (3.141 becomes 3.15, -3.141 becomes -3.15). ROUNDDOWN rounds toward zero (truncation). They ignore the standard 5+ rule.
🔽🔼 Floor & Ceiling

=FLOOR( ) / =CEILING( )

=FLOOR(7.8) → returns 7 (round DOWN to nearest integer)
=CEILING(7.2) → returns 8 (round UP to nearest integer)
📢 Plain English: "FLOOR = look DOWN at the floor. Whatever integer is below you, that's it. CEILING = look UP at the ceiling. Whatever integer is above you, that's it."

🔧 Analogy: The floor jack pushes DOWN. The ceiling fan is UP above you.
🗄️ SQL Equivalent
SELECT FLOOR(7.8), CEILING(7.2); -- returns 7, 8
⚠️
Exam Tip: FLOOR and CEILING can take a second argument for significance: =FLOOR(23, 5) = 20 (nearest multiple of 5 going down). =CEILING(23, 5) = 25 (nearest multiple of 5 going up).
❓ Quick Recall: =FLOOR(4.9) and =CEILING(4.1) -- what do they return?
FLOOR(4.9) = 4 (down to the floor). CEILING(4.1) = 5 (up to the ceiling).
🔧 Wrench

=MOD( ) -- The Remainder

=MOD(17, 5) → returns 2
^ ^^^ ^^ ^
| | | |
| | | +-- 🟡 DIVISOR: divide by this
| | +-- 🟠 NUMBER: the number to divide
| +-- 🔵 MOD = modulo = "what's LEFT OVER after division?"
+-- ⚡ Formula start
📢 Plain English: "17 divided by 5 = 3 with remainder 2. MOD gives you that remainder: 2."

🔧 Analogy: A wrench turning a bolt. After full turns, MOD tells you how much of a partial turn is left over.
🗄️ SQL Equivalent
SELECT 17 % 5; -- returns 2 (or MOD(17, 5) in some databases)
🔧 BSP Real Data
=MOD(10509, 7) = 3 -- 10,509 jobs divided into weeks of 7 = 1,501 full weeks with 3 extra days. Useful for cycle analysis.
⚠️
Exam Tip: =MOD(x, 2) is a classic trick to test odd/even. If result = 0, x is even. If result = 1, x is odd. Combine with IF: =IF(MOD(A1,2)=0,"Even","Odd")
❓ Quick Recall: =MOD(100, 3) = ?
1. 100 / 3 = 33 remainder 1.

🌻 Room 7: Backyard -- Wildcards

Step into the backyard and see wildflowers. Two patches: the star patch (*) that grows ANY number of flowers, and the single bloom (?) that always grows exactly ONE flower.

🔤 Mnemonic: "Star = Any Flowers, Question = One Flower"
* = zero or more characters (like a field of ANY number of wildflowers)
? = exactly ONE character (like ONE single bloom)
🌿 Wild Patterns

Wildcards: * and ?

=COUNTIF(A:A, "Sew*") → matches Sewer, Sewing, Sewage, Sew
=COUNTIF(A:A, "Pip?") → matches Pipe, Pips but NOT Piped (5 chars)
=COUNTIF(A:A, "*repair*") → matches anything CONTAINING "repair"
📢 Plain English:
* (asterisk/star) = "any number of characters, including zero." "Sew*" matches "Sew", "Sewer", "Sewing", "Sewage". The star eats anything after "Sew".

? (question mark) = "exactly one character." "Pip?" matches "Pipe" and "Pips" (4 characters total) but NOT "Piped" (5 characters).

*text* = "contains" pattern. Stars on BOTH sides mean "anything before AND anything after."
🔧 BSP Real Data
=COUNTIF(job_type, "*sewer*") -- counts any job with "sewer" anywhere in the type name
=SUMIF(job_type, "Drain*", total) -- sums revenue for all "Drain..." job types (Drain Cleaning, Drain Repair, etc.)
⚠️
Exam Tip: Wildcards work in COUNTIF, SUMIF, AVERAGEIF, and their "S" versions. They do NOT work in regular IF. To search with a literal * or ?, prefix with tilde: "~*" matches an actual asterisk.
❓ Quick Recall: =COUNTIF(A:A, "Jo?n") -- would this match "John"? What about "Joan"? What about "Joann"?
John = YES (4 chars, ? = h). Joan = YES (4 chars, ? = a). Joann = NO (5 chars -- ? only matches exactly ONE character).

🛏️ Room 8: Upstairs Bedroom -- ARRAYFORMULA

One alarm clock goes off and the ENTIRE room wakes up at once. One formula, applied to every row simultaneously.

⏰ One Alarm

=ARRAYFORMULA( ) -- One Formula, All Cells

=ARRAYFORMULA(B2:B * C2:C)
^ ^^^^^^^^^^^^ ^^^^ ^ ^^^^
| | | | |
| | | | +-- 📗 RANGE 2: second column
| | | +-- 🟠 OPERATOR: multiply (or any operation)
| | +-- 📗 RANGE 1: first column
| +-- 🔵 ARRAYFORMULA = "apply to ALL rows at once"
+-- ⚡ Formula start
📢 Plain English: "Instead of writing a formula in every row (B2*C2, B3*C3, B4*C4...), write ONE ARRAYFORMULA in row 2 and it fills down automatically for every row. One alarm clock, everyone wakes up."

⚠️ Google Sheets specific. Excel uses Ctrl+Shift+Enter for array formulas (or dynamic arrays in modern Excel).
🗄️ SQL Equivalent
SELECT quantity * unit_price AS line_total FROM order_items; -- applies to all rows
🔧 BSP Real Data
=ARRAYFORMULA(IF(job_total > 2120, "Above Avg", "Below Avg"))
One formula labels ALL 10,509 jobs as above or below the $2,120 average ticket. Without ARRAYFORMULA, you'd need 10,509 individual IF formulas.
⚠️
Exam Tip: ARRAYFORMULA is Google Sheets. In Excel, array formulas use Ctrl+Shift+Enter (curly braces appear). Modern Excel 365 has dynamic arrays that spill automatically without CSE.
❓ Quick Recall: What's the benefit of ARRAYFORMULA over copying a formula down 10,000 rows?
One formula, one cell, auto-expands. Faster processing, easier to maintain, impossible to have broken rows. Change the formula once, it updates everywhere.

🛋️ Room 9: Guest Room -- IMPORTRANGE

A guest arrives with a suitcase full of data from ANOTHER house (spreadsheet). They unpack it right into your room.

📦 Guest's Suitcase

=IMPORTRANGE( ) -- Pull Data From Another Spreadsheet

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:D100")
^ ^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^
| | | |
| | | +-- 📗 RANGE: "SheetName!CellRange"
| | +-- 🟡 URL: the OTHER spreadsheet's URL
| +-- 🔵 IMPORTRANGE = "bring data from another spreadsheet"
+-- ⚡ Formula start
📢 Plain English: "Go to this OTHER Google Spreadsheet (the URL), find Sheet1, grab cells A1 through D100, and bring that data here. It stays linked -- when the source updates, this updates too."

📦 Analogy: A guest arrives from another house carrying a suitcase. They unpack the suitcase (data) into your guest room. When they go home and pack new stuff, your copy updates.
⚠️
Exam Tip: IMPORTRANGE is Google Sheets only. First use requires clicking "Allow access" to grant permission. The URL must be a full Google Sheets URL. Excel uses Power Query or external data connections instead.
❓ Quick Recall: Does IMPORTRANGE data update automatically when the source spreadsheet changes?
Yes! It's a live link. Changes in the source spreadsheet reflect automatically (with some caching delay).

🏆 Room 10: Basement -- MAXIFS / MINIFS

Down in the basement is a locked trophy case. The biggest and smallest awards are here -- but only for winners who meet the conditions.

🔤 Mnemonic: "The Conditional Trophy Case"
MAXIFS = biggest trophy that meets the criteria
MINIFS = smallest trophy that meets the criteria
Both use the AWW pattern (output range FIRST, like SUMIFS)
🥇 Biggest

=MAXIFS( ) -- Largest Value With Conditions

=MAXIFS(G2:G21, B2:B21, "Sewer")
^ ^^^^^^ ^^^^^^^ ^^^^^^^ ^^^^^^^^
| | | | |
| | | | +-- 🟡 CRITERIA: filter value
| | | +-- 📗 CRITERIA RANGE: where to check
| | +-- 🟣 MAX RANGE: find the biggest IN HERE (⚠️ FIRST!)
| +-- 🔵 MAXIFS = "max value where conditions are met"
+-- ⚡ Formula start
📢 Plain English: "Find the BIGGEST number in column G, but ONLY look at rows where column B = 'Sewer'. Ignore all non-sewer rows."

🏆 Analogy: "What's the biggest trophy in the case? But wait -- only look at the gold section (the condition). What's the biggest gold trophy?"
🗄️ SQL Equivalent
SELECT MAX(total) FROM jobs WHERE job_type = 'Sewer';
🔧 BSP Real Data
=MAXIFS(total, job_type, "Sewer") = the biggest single sewer job
With avg sewer ticket at $8,679, the MAX sewer job could be well above $15K for a full line replacement.
❓ Quick Recall: Does MAXIFS put the max_range first or last?
FIRST! All "S" functions (SUMIFS, AVERAGEIFS, MAXIFS, MINIFS) put the output range first. AWW pattern.
🥉 Smallest

=MINIFS( ) -- Smallest Value With Conditions

=MINIFS(G2:G21, B2:B21, "Sewer")
^ ^^^^^^ ^^^^^^^ ^^^^^^^ ^^^^^^^^
| | | | |
| | | | +-- 🟡 CRITERIA: filter value
| | | +-- 📗 CRITERIA RANGE: where to check
| | +-- 🟣 MIN RANGE: find the smallest IN HERE (⚠️ FIRST!)
| +-- 🔵 MINIFS = "min value where conditions are met"
+-- ⚡ Formula start
📢 Plain English: "Find the SMALLEST number in column G, but ONLY look at rows where column B = 'Sewer'. What's the cheapest sewer job we've done?"
🗄️ SQL Equivalent
SELECT MIN(total) FROM jobs WHERE job_type = 'Sewer';
🔧 BSP Real Data
=MINIFS(total, job_type, "Sewer") = the smallest sewer job
Could be a diagnostic/camera inspection at a few hundred dollars. Useful for understanding the floor of sewer pricing.
❓ Quick Recall: Name all 4 "S" functions that put the output range FIRST.
SUMIFS, AVERAGEIFS, MAXIFS, MINIFS. All use AWW (Add/output range first, Where, What).

🔁 Spaced Repetition Review Schedule

The forgetting curve is brutal: you lose 70% within 24 hours without review. This schedule beats it.

Day 1
First Learning
Walk through ALL 10 rooms. Read every formula. Do every quiz question. Don't try to memorize -- just expose.
Day 3
First Review
Cover the formulas and try to write them from memory. Walk through the house mentally. Which rooms feel weak? Focus there.
Day 7
Active Recall
Do ONLY the quiz questions. No peeking. Score yourself. Any question you miss, re-study that room for 5 minutes.
Day 14
Application
Open a blank spreadsheet. Build each formula from memory using BSP data. Can you write SUMIFS without looking? COUNTIFS?
Day 30
Exam Simulation
Time yourself. 25 formulas, write the syntax + explain the argument order. If you can do all 25 in under 15 minutes, you're exam-ready.

⚡ The 3-Minute Daily Drill

Every morning, close your eyes and mentally walk through the house:
1. 🚪 Front Door: Name the Big 5 (SACMM)
2. 🍳 Kitchen: Mean vs Median vs Mode
3. 🛋️ Living Room: COUNTIF(where, what) / COUNTIFS(where1, what1, where2, what2)
4. 🍽️ Dining Room: SUMIF = WWA / SUMIFS = AWW ("Aww!")
5. 💼 Office: IF(test, yes, no) / IFS(test1, val1... TRUE, default)
6. 🔧 Garage: ABS, ROUND, FLOOR, CEILING, MOD
7. 🌻 Backyard: * = any, ? = one
8. 🛏️ Bedroom: ARRAYFORMULA = one alarm, all rows
9. 🛋️ Guest Room: IMPORTRANGE("url", "range")
10. 🏆 Basement: MAXIFS/MINIFS = AWW (output first)

Under 3 minutes = you own these formulas.

📋 Master Cheat Sheet -- Room by Room

The entire house on one table. Print this, tape it to your wall, review daily.

Room Formula Syntax Arg Order BSP Example
🚪 Front DoorSUM=SUM(range)range$6,456,070 total revenue
🚪 Front DoorAVERAGE=AVERAGE(range)range$2,120 avg ticket
🚪 Front DoorCOUNT=COUNT(range)range10,509 completed jobs
🚪 Front DoorMAX=MAX(range)range$41,356 biggest job
🚪 Front DoorMIN=MIN(range)range$24 smallest job
🍳 KitchenMEDIAN=MEDIAN(range)range$626 true typical ticket
🍳 KitchenMODE=MODE(range)range~$89 (dispatch fee)
🛋️ Living RoomCOUNTIF=COUNTIF(range, criterion)Where, What9 sewer jobs
🛋️ Living RoomCOUNTIFS=COUNTIFS(r1, c1, r2, c2)Where, What pairsSewer jobs > $5K
🍽️ Dining RoomSUMIF=SUMIF(range, crit, sum_range)WWA$26,038 sewer revenue
🍽️ Dining RoomSUMIFS=SUMIFS(sum_range, r1, c1...)AWW ⚠️ FLIPPED!Sewer revenue > $5K
🍽️ Dining RoomAVERAGEIF=AVERAGEIF(range, crit, avg_range)WWA$8,679 avg sewer ticket
🍽️ Dining RoomAVERAGEIFS=AVERAGEIFS(avg_range, r1, c1...)AWW ⚠️ FLIPPED!Avg completed sewer
💼 OfficeIF=IF(test, true, false)test, yes, noAbove/Below $2,120
💼 OfficeIFS=IFS(t1,v1, t2,v2, TRUE,def)test/value pairsTech tier labels
🔧 GarageABS=ABS(value)valueRevenue delta magnitude
🔧 GarageROUND=ROUND(value, places)value, decimal places$614.35 per job
🔧 GarageROUNDUP/DOWN=ROUNDUP(val, places)value, decimal placesForce direction
🔧 GarageFLOOR/CEILING=FLOOR(val) / =CEILING(val)value [, significance]Whole number rounding
🔧 GarageMOD=MOD(number, divisor)number, divisorOdd/even check
🌻 BackyardWildcards* = any chars, ? = one charin criteria only"Sew*" matches Sewer
🛏️ BedroomARRAYFORMULA=ARRAYFORMULA(expr)any expression with rangesLabel all 10,509 jobs
🛋️ Guest RoomIMPORTRANGE=IMPORTRANGE("url","range")url, "Sheet!Range"Cross-sheet data pull
🏆 BasementMAXIFS=MAXIFS(max_range, r1, c1)AWW ⚠️Biggest sewer job
🏆 BasementMINIFS=MINIFS(min_range, r1, c1)AWW ⚠️Smallest sewer job

🔑 THE ONE PATTERN THAT RULES THEM ALL

Non-S functions (SUMIF, AVERAGEIF): output range is LAST = WWA (Where, What, Add)
S functions (SUMIFS, AVERAGEIFS, MAXIFS, MINIFS): output range is FIRST = AWW (Add, Where, What)

🧠 Memory trick: "The S makes it Switch! AWW -- it's backwards!"

If you remember nothing else from this guide, remember THIS. It's the #1 exam trap.

📊 Mean vs Median: The BSP Proof

Mean (AVERAGE): $2,120 -- pulled up by outlier jobs ($41K max)
Median: $626 -- the TRUE middle ticket
Gap: $1,494 = outliers are present

📌 Exam rule: Mean > Median = Right-skewed (positive skew). A few large values pull the mean right.
📌 When to use which: Use MEDIAN when outliers exist. Mean is misleading with skewed data.
📌 BSP proves it: If you told Stephanie "our average ticket is $2,120" she'd think typical jobs are $2K. The truth is $626. The median doesn't lie.

CompTIA Data+ Formula Memory Palace
Built for Robert Dove | Dove Web Consulting
Method of Loci + Chunking + Spaced Repetition + Active Recall + Visual Encoding + Mnemonics + Analogies
25 formulas • 10 rooms • 7 memory techniques • Real BSP data ($6.4M, 10,509 jobs)
Walk the house. Own the formulas. Pass the exam.