Quick answer: Calculate a material order in three stages: measured quantity × (1 + allowance), round the resulting pack count up to whole packs, then multiply by pack size. The measurement and pack size must use the same unit. This tutorial works through the formulas in the free Excel and printable PDF takeoff download.
Set one measurement basis for each row
In the workbook, C is Measured qty, D is Unit, E is Allowance %, and F is Pack size (same unit). G–I hold the results. Row 10 is the first prepared input row. Complete Item in A and Drawing / location in B as well, so the quantity has an identifiable source.
Pack size is a quantity, not the word box, roll, or bundle. If you measure in meters and buy 25-meter packs, enter m as the unit and 25 as the pack size. If supplier coverage is expressed in another unit, convert it before using the formula. Record the specification and the basis of any conversion in Notes.
Step 1: apply the allowance once
For a complete, valid row, the calculation in G10 is =C10*(1+E10). The shipped workbook wraps the calculation in input checks. Enter a percentage in E10, such as 5%; a numeric value of 5 means 500%, not 5%. The workbook accepts non-negative allowances without an upper cap, so a numeric input can still be inappropriate for the job.
Hypothetical example: measured quantity 120 m, allowance 5%, pack size 25 m. Adjusted quantity is 120 × (1 + 0.05) = 126 m. The 5% is an example input, not a recommended waste factor. Choose an allowance from the material, layout, specification, and project evidence; do not substitute a universal trade percentage.
Step 2: round the pack count, then calculate the order
For valid inputs, H10 uses =ROUNDUP(G10/F10,0). I10 then uses =H10*F10. Whole packs are counted separately from the quantity they contain.
| Stage | Calculation | Result |
|---|---|---|
| Adjusted quantity | 120 × 1.05 | 126 m |
| Pack ratio | 126 ÷ 25 | 5.04 packs |
| Whole packs | Round 5.04 up to a whole number | 6 packs |
| Ordered quantity | 6 × 25 | 150 m |
The order contains 24 m more than the adjusted requirement because only complete packs are purchased in this example. Do not add another 5% after rounding unless it represents a separate, documented requirement. Before using the result, verify the supplier's actual selling increment and whether the items can be ordered in partial packs.
Check zero allowances and decimal quantities
A second hypothetical row uses 12 each, 0% allowance, and a pack size of 1 each. Its outputs are 12 adjusted, 12 whole packs, 12 ordered. Zero allowance is a valid choice; blank allowance is not the same input.
The third hypothetical row uses 7.5 m, 10% allowance, and packs of 2.5 m. Adjusted quantity is 7.5 × 1.10 = 8.25 m. Dividing by 2.5 gives 3.3 packs; rounding up gives 4 packs, containing 10 m. The 10% is another demonstration input, not a recommended allowance.
The workbook applies strict ROUNDUP to the calculated ratio without a tolerance that would erase a small positive remainder. Spreadsheet arithmetic has finite precision: an intended exact decimal boundary can be represented slightly above a whole number and produce an extra pack. Check such boundaries manually against the measurement and the supplier increment before ordering. Do not treat a displayed rounded quantity as proof that the underlying ratio is exact.
Understand blank results and Check inputs
| Input state | Displayed result in G–I | Action |
|---|---|---|
| A required A–F field is missing, and no supplied numeric input is invalid | Blank | Complete the item, drawing/location, quantity, unit, allowance, and pack size. |
| A supplied C, E, or F value is non-numeric; C or E is negative; or F is zero or negative | Check inputs | Correct the invalid numeric input. This warning takes priority even if another required field is missing. |
| Measured quantity is 0, allowance is 0%, pack size is positive, and the other required fields are complete | 0 adjusted, 0 whole packs, 0 ordered | Confirm that zero is intentional rather than a missed measurement. |
Notes is optional. Keep input values in A–F and J; replacing formulas in G–I removes the workbook's calculation and checks. Numeric validation does not decide whether the material specification, allowance, or measurement is correct.
Keep ordering groups and units consistent
Rounding happens per row. Two locations needing 12 m each, with zero allowance and 25 m packs, produce two packs if kept as separate orders; their combined 24 m needs one pack if the material specification and purchasing requirements allow a shared order. Decide that grouping explicitly and preserve the location breakdown.
Do not create a grand total by adding meters, square feet, cubic yards, and individual items. Review each unit and specification separately. This worksheet tracks material quantities; pricing, labor, delivery, and other estimate costs belong in a separate cost build-up. The workbook has no grand total across unlike units.
Frequently asked questions
Why is ordered quantity larger than adjusted quantity?
Adjusted quantity includes the allowance. Ordered quantity also accounts for whole packs. With 126 m required and 25 m in each pack, six packs contain 150 m. The extra 24 m comes from pack rounding, not a second allowance.
Can I use a zero allowance?
Yes. Enter 0% rather than leaving the allowance cell empty. A zero allowance preserves the measured quantity before pack rounding; a missing allowance leaves an otherwise valid row incomplete.
What does pack size mean when my takeoff uses area or length?
It is the quantity in one purchasable pack, expressed in the same unit as the measured quantity. For a takeoff in square feet, use square feet per pack. Convert supplier information first if its unit differs.
Can I combine several locations before rounding?
Only combine quantities that belong to the same material specification, unit, and purchasing group. Keep the drawing and location breakdown in your records. Rounding separate rows can produce a different order from rounding their combined quantity.