Grid Mechanics, Operators & Reference Locks

Who this is for

Stage 1: Grid Foundations & Reference Architecture — Designed for learners mastering cell coordinates, operator precedence, absolute $b$1 locks, mixed $a2 & b$1 matrices through interactive spreadsheet examples.

What you will practise

• Write live multiplication and addition formulas referencing exact grid coordinates so rental totals update under rate and zero-day edge cases.
• Apply parentheses deliberately to override default * and / precedence when computing batch contribution profit and unit margin percentage.
• Construct a relative row formula in D2 and fill D2:D5 so every SKU row computes its own total shipment weight dynamically.
• Combine a relative order-value cell (B4) with an absolute parameter cell ($B$1) and fill C4:C6 cleanly.
• Build a dynamic total with SUM and compute 100%-reconciling share ratios across C2:C5 using $B$6.
• Apply a row-locked mixed reference (C$2) to calculate off-peak discounted rates down a cabin rate list.
• Write a column-locked formula ($B4*C$3 or $B4*C3) in C4 and fill right across C4:E4 for courier zones 1–3.
• Write =$B4*C$2 in C4 and populate all nine cells C4:E6 via vertical and horizontal fills.

Your learning path

1. 2D Matrix Dynamic Row Sum, Column Sum & Two-Way Lookup (SUMPRODUCT)

   Sum an entire dynamically matched row, sum an entire dynamically matched column, or retrieve a two-way matrix intersection across a 2D table using boolean-broadcast SUMPRODUCT.

2. Dynamic two-way sum and average: aggregate a chosen column by row criteria

   Select a monthly metric column with XLOOKUP, filter its rows, and calculate a dynamic sum or average.

3. Lookup Hourly Rates & Sum Total Invoice Billing (XLOOKUP & SUMPRODUCT)

   Pull role-based hourly billing rates from a master rate card with XLOOKUP and roll up total project invoice value via SUMPRODUCT(Hours, Looked_Up_Rates).

4. Sum matching rows AND columns in a 2D matrix (SUMPRODUCT outer product)

   Aggregate a 2D crosstab when BOTH row labels and column headers can repeat (e.g., multiple "North" rows across multiple "Q1" columns).

5. 3D references: sum the same cell across consecutive monthly worksheets

   Consolidate identical template cells across tab ranges like Jan:Dec!B5 using 3D worksheet references.

6. Casagrande Electro-Osmotic Clay Dewatering & Power Formula

   Calculate Electric Voltage Gradient ie = V/L (V/m), Electro-Osmotic Cathode Water Flow Qe (L/h), DC Rectifier Ohmic Power (kW), and Specific Dewatering Energy (kWh/m³).

7. Sum an entire 2D matrix across columns matching a header tag (SUM & FILTER)

   Total a multi-row, multi-column 2D table (B3:E5) across ONLY the columns whose Row-2 status tag equals "Active"—solving the classic 2D SUMIF limitation cleanly!

8. Sum and find MAX of every Nth horizontal column (repeating Budget vs Actual blocks)

   Sum or find the maximum across columns selected by repeated headers, such as Actual and Budget.