Core Aggregations, Rounding Discipline & Cross-Sheet Links

Who this is for

Stage 1: Grid Foundations & Reference Architecture — Designed for learners mastering sum / average / median, count / counta / countblank, round / roundup / int / mod, cross-sheet ! references through interactive spreadsheet examples.

What you will practise

• Compute a section subtotal with SUM and aggregate two non-adjacent bakery wings into an accurate grand total.
• Use AVERAGE across two 5-hour telemetry columns to observe and verify blank-skipping versus zero-inclusion.
• Build a three-row completeness summary in D2:D4 using COUNT, COUNTA, and COUNTBLANK over B2:B7.
• Compute minimum sensor reading, maximum sensor reading, and operating temperature spread across B2:B7.
• Apply ROUND(..., 2) to invoice line items in E2:E4, sum them in E5, and isolate the rounding discrepancy in E6.
• Compute whole pallets required in D2 with ROUNDUP(..., 0) and complete emergency kits in D3 with ROUNDDOWN(..., 0).
• Populate Full cartons (C4:C6) with INT and Loose units (D4:D6) with MOD referencing locked carton capacity $B$1.
• Calculate weekly absolute error in D2:D5 using ABS and summarize Mean Absolute Error (MAE) in D6 with AVERAGE.

Your learning path

1. Weighted average in Excel: calculate unit cost with SUMPRODUCT

   Three deliveries have different quantities and unit prices. Calculate the cost of one average unit, check it against the invoice totals, and handle a list with no units.

2. Count Rows Meeting Internal Column-vs-Column Criteria (SUMPRODUCT)

   Count table rows where one column exceeds or precedes another column on the same row—such as Actual > Budget AND Ship_Date <= Due_Date—using Boolean SUMPRODUCT.

3. Count & Sum Odd vs Even Numbers in a Range (MOD, ISODD & SUMIF)

   Test integer parity with MOD(n, 2) or ISODD/ISEVEN and count or sum odd-numbered vs even-numbered street addresses, lot IDs, and quantities cleanly across any spreadsheet engine.

4. Case-sensitive sum and count with EXACT helper flags

   Prevent SUMIF and COUNTIF from merging case-distinct IDs like "001XyzAB" and "001Xyzab" by pairing EXACT with SUMPRODUCT.

5. Bundle and quote pricing with SUMPRODUCT (quantities, unit prices, and discounts)

   Compute gross subtotals, line-discounted net totals, and total savings in a single cell with SUMPRODUCT—no line-extension column required.

6. Check if Multiple Columns Are Equal (Row Audit & SUMPRODUCT Count)

   Verify whether three or more system export columns agree on every row using chained pairwise AND(B2=C2, B2=D2) tests and count fully synchronized rows across a table with SUMPRODUCT.

7. Flag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)

   Detect cold-chain, blood-glucose, or machining readings that fall strictly outside an acceptable [Min, Max] window using OR(B2 < MIN(X,Y), B2 > MAX(X,Y)) and count total two-sided excursions via COUNTIF.

8. Cell Equals, Count, or Sum One of Many Values (Allowlist COUNTIF + SUMPRODUCT)

   Replace brittle 10-way OR chains with a range-driven COUNTIF allowlist flag to test, count, and sum rows matching any value in an editable lookup list.