Swapping matrix axes
Rows are carriers and columns are parcels. A nonsquare source makes the mistake visible.
Build a carrier by parcel shipping quote matrix
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. F2 selects the first 1–4 carriers in A2:D5; F3 selects the first 1–5 parcel weights in A12:E12. Return a numeric matrix with carriers as rows and parcels as columns, in source order, with no headings. Each quote is Base fee plus billable extra kg times Rate per kg, rounded to two decimals. Extra kg is MAX(0, parcel weight minus Included kg), rounded up to the next 0.5 kg. Exact increments stay unchanged. Even a zero-weight parcel pays the base fee. Unselected output positions remain empty.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:K8. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Generate every selected carrier/weight combination using included weight and half-kilogram billing increments.
THE QUICK ANSWER01
Generate every selected carrier/weight combination using included weight and half-kilogram billing increments.
ƒx
=MAKEARRAY(F2,F3,LAMBDA(carrier,parcel,LET(base,INDEX(B2:B5,carrier),included,INDEX(C2:C5,carrier),rate,INDEX(D2:D5,carrier),weight,INDEX(A12:E12,1,parcel),ROUND(base+CEILING.MATH(MAX(0,weight-included),0.5)*rate,2))))| G | H | I | J | K |
|---|---|---|---|---|
| 4.25 | 4.25 | 4.25 | 5.43 | 8.95 |
| 3.1 | 3.1 | 4.1 | 5.09 | 8.08 |
| 6 | 6 | 6 | 6 | 9.01 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Read the first carrier across parcels
Birch charges 4.25 including 1 kg, then 2.35 per extra kg billed in half-kilogram increments. Weights 0, 0.5 and 1 kg each cost 4.25. A 1.01 kg parcel bills 0.5 extra kg and costs 5.43. A 2.51 kg parcel bills 2 extra kg and costs 8.95.
Separate dimensions from source capacity
Four carriers and five weights are available, but F2 and F3 select a prefix of each source. A 1 by 1 quote is a valid result. The maximum reserved area still participates in checking so stale trailing results cannot pass.
Edit within the input contract
All four carrier rows require numeric base fees and per-kg rates from 0 through 1,000 with at most two decimals, and included weights from 0 through 100 kg with at most two decimals. All five parcel weights follow the same weight bounds. F2 and F3 are numeric integers in the stated ranges. Text numbers, blanks and Boolean controls are not numeric inputs. Keep the output area clear. These are fictional teaching prices, not a carrier tariff.
Read the full step-by-step walkthrough
- Set the output dimensions
MAKEARRAY reads F2 rows and F3 columns. Its two LAMBDA parameters receive one-based output coordinates.
- Read one carrier
The row coordinate selects the base fee, included weight and rate from the same source carrier. LET names these local values.
- Read one parcel weight
The column coordinate selects a weight from the horizontal source A12:E12. INDEX uses row 1 and column parcel.
- Apply included weight
Only excess weight is billable. MAX prevents a weight below the allowance from producing a negative extra charge.
- Round weight up, then quote to cents
CEILING.MATH uses 0.5 kg increments. Add the base fee after pricing extra kg, then round the final quote to two decimals.
Things to watch
Rounding extra weight to the nearest half kg
Billing rounds upward. A 1.01 kg parcel with 1 kg included still bills 0.5 extra kg.
Omitting the base fee for zero weight
The base fee applies to every selected parcel under this exercise's pricing rule.
About this example
Bounded original teaching inputs and source-only assessment; actual browser calculation and spill checks are separate from native-client recalculation and edit/save.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.