Quantity takeoff in Excel: which measured quantity is over the estimate
A quantity takeoff is one row per work item. You type the item, the trade, the unit, the estimated quantity, the measured quantity, and the unit rate. The gap is measured minus estimated while the row is open and both quantities are filled. The window says over when that gap is above zero, short when it is below zero, and match when the two quantities are equal. The amount is measured times the rate, and it stays blank when the rate is blank. The first screen is a sheet named Takeoff. Row 1 is the header. Row 2 is one item you can check by hand: estimate 100, measured 120, rate 45, status open. The gap is 20. The amount is 5400. The window is over. There is no site percent and no minute standard on that screen. This sheet is not a daily site log. That log uses a date and a percent complete. This list uses the two quantities you typed. It is not a routing sheet of standard minutes, and it is not an event budget of planned money against money spent. The trade is a word you type so you can filter. You type both quantities. The question is which open row was measured above the estimate. Closed rows drop out of the window. They stay on the sheet. A negative gap is short, not a number to hide. A missing rate blanks the amount and leaves the gap. Elyx can write the gap, the amount, and the window. You still type the estimate, the measure, and the rate. The trial lasts 7 days, with no card.
The Problem
The takeoff fails when the measure lives in a marked-up drawing. A sentence says the slab was more than the estimate. Nobody can add the gaps, because the estimate and the measure are not on the same row. A sentence without those two numbers is not a takeoff. A second failure is mixing this sheet with a site percent, a routing time, or an event budget. A site row answers which task percent you typed today. A routing row answers which operation is over the standard minutes. A budget row answers which cost line is over the planned amount, with no quantity. If you paste percents, minutes, or event lines here, the morning sum no longer means measured minus estimated. Keep this sheet to items, quantities, and the rate you typed. A third failure is deleting an item when the measure is agreed. Set the status to closed and leave the row. The gap goes blank. Deleting the row removes the only record of the 100 and the 120 you already typed. The sheet still does not decide what the estimate should have been.
Benefits
Each item is one row with an estimated quantity, a measured quantity, and a unit rate you typed.
The gap is measured minus estimated while the row is open.
Over means the measure is above the estimate. Short means it is below. Match means the two quantities are equal.
The amount is measured times the rate, and it stays blank when the rate is blank.
Closed rows stay on the sheet and drop out of the over count.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Takeoff. In row 1 type: Item, Trade, Unit, Estimate, Measured, Rate, Status, Gap, Amount, Window. Item is the work line. Trade and Unit are words you type. Estimate and Measured are quantities. Rate is the unit rate you typed, not a formula. Gap, Amount, and Window are formulas. Status is a word from a list. Freeze row 1. Do not merge cells.
One row per item. A second measure of the same item is a second row. Do not pack two measures into one cell.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source on a sheet named Lists is open and closed. Open means the gap is still yours to read. Closed means you are done with that item. Only open stays in the window. People must pick a word. A synonym such as agreed or done will not match the formulas.
Estimate, Measured, and Rate are numbers. Do not type the word over in those columns.
Enter five items you can check by hand
On row 2 type item Slab, unit m2, estimate 100, measured 120, rate 45, status open. You will predict gap 20, amount 5400, window over. On row 3 type Wall, estimate 80, measured 80, rate 30, status open. Gap should be 0, amount 2400, window match. On row 4 type Pipe, estimate 50, measured 40, rate 12, status open. Gap should be -10, amount 480, window short. On row 5 type Paint, estimate 20, measured 25, rate blank, status open. Gap should be 5, amount blank, window over. On row 6 type Door, estimate 10, measured 12, rate 200, status closed. Gap, amount, and window should go blank. Use these numbers before you trust the formulas.
If you do not know the rate yet, leave Rate blank. The amount stays blank. The gap still calculates from the two quantities.
Write the gap
The gap is blank when status is closed, or when Estimate or Measured is blank. Otherwise it is Measured minus Estimate. With Estimate in D2, Measured in E2, and Status in G2, use =IF(OR(G2="closed",D2="",E2=""),"",E2-D2). Format as a number. A positive number was measured above the estimate. Zero means the quantities match. A negative number was measured below the estimate. The rate is not in this formula.
=IF(OR(G2="closed",D2="",E2=""),"",E2-D2)120 minus 100 is 20. 40 minus 50 is -10. If you see another number, the cells are not the quantities you typed.
Write the amount and the window
The amount is blank when status is closed, or when Measured or Rate is blank. Otherwise it is Measured times Rate. Use =IF(OR(G2="closed",E2="",F2=""),"",E2*F2). The window is blank when status is closed or a quantity is blank. Otherwise it is over when Measured minus Estimate is above zero, short when that difference is below zero, and match when it is zero. Use =IF(OR(G2="closed",D2="",E2=""),"",IF(E2-D2>0,"over",IF(E2-D2<0,"short","match"))). 120 times 45 is 5400. A blank rate on Paint leaves the amount blank and still marks the gap of 5 as over.
=IF(OR(G2="closed",D2="",E2=""),"",IF(E2-D2>0,"over",IF(E2-D2<0,"short","match")))Do not wrap the gap in ABS. Hiding the minus sign hides the pipe that was measured 40 against an estimate of 50.
Count the over rows and add the gaps
On a sheet named Morning, the over rows are =COUNTIFS(Takeoff!J:J,"over"). With the five rows above, that count is 2: the gap of 20 and the gap of 5. The over gap sum is =SUMIFS(Takeoff!H:H,Takeoff!J:J,"over"), which is 25. The over amount is =SUMIFS(Takeoff!I:I,Takeoff!J:J,"over"), which is 5400, because Paint has no rate. The short count is =COUNTIFS(Takeoff!J:J,"short"), which is 1, the gap of -10. Closed Door is out because its window is blank. A blank window is not over. Write the rule next to the count.
=SUMIFS(Takeoff!H:H,Takeoff!J:J,"over")Do not add the -10 into the over sum. Short is a different word, so SUMIFS leaves it out.
Read a missing rate as a blank amount
If the gap is 5 and the rate is blank, the window can still say over. The amount stays blank. That is a row to price, not a row to delete. Do not type a rate you do not have. A gap on a closed row should not happen, because closed blanks the cell. If you see a number on a closed row, the formula was overwritten. Door would have been a gap of 2 and an amount of 2400, but closed keeps both off the morning list.
Filter Window to over for the morning list. Filter Trade when you only want one word. Filter Status to closed when you need history.
Check the five rows before you share the sum
Estimate 100 and measured 120, rate 45, status open: gap 20, amount 5400, window over. Estimate 80 and measured 80, rate 30: gap 0, amount 2400, window match. Estimate 50 and measured 40, rate 12: gap -10, amount 480, window short. Estimate 20 and measured 25, rate blank: gap 5, amount blank, window over. Estimate 10 and measured 12, rate 200, status closed: gap blank, amount blank, window blank. The over count is 2, the over gap sum is 25, and the over amount is 5400. If a check fails, fix the quantities, the rate, or the status word before you send the sum. The sum is only this sheet. It is not a site percent, and it is not a minute standard.
Write the five checks on Morning if more than one person updates the takeoff.
Template Features
Gap while the item is open
Measured minus estimated. Blank when status is closed or a quantity is empty.
=IF(OR(G2="closed",D2="",E2=""),"",E2-D2)Amount from the rate you typed
Measured times rate. Blank when the rate is empty.
=IF(OR(G2="closed",E2="",F2=""),"",E2*F2)Window from the gap
Over when the gap is above zero, short when it is below zero, match when it is zero.
=IF(OR(G2="closed",D2="",E2=""),"",IF(E2-D2>0,"over",IF(E2-D2<0,"short","match")))Over gap sum
SUMIFS adds only gaps whose window is over. The sample sum is 25.
=SUMIFS(Takeoff!H:H,Takeoff!J:J,"over")History stays on the sheet
Closed rows remain. They leave the over count because the window is blank.
Concrete Examples
A gap of 20
Slab was estimated at 100 square metres and measured at 120. The rate you typed is 45.
Estimate: 100. Measured: 120. Rate: 45. Status: open.
Result: Gap is 20. Amount is 5400. Window is over. The over count includes the row.
A measure below the estimate
Pipe was estimated at 50 and measured at 40. You do not want that row in the over sum.
Estimate: 50. Measured: 40. Rate: 12. Status: open.
Result: Gap is -10. Amount is 480. Window is short. The over gap sum does not include -10.
A gap with no rate
Paint was estimated at 20 and measured at 25. The rate cell is empty.
Estimate: 20. Measured: 25. Rate: blank. Status: open.
Result: Gap is 5. Window is over. Amount is blank. The over count is 2 with the slab, and the over amount stays 5400.
Pro Tips
A blank rate is not zero
Leave Rate blank when you do not have it. Typing 0 makes the amount 0, which is a number, not a missing price. 120 times 0 is 0.
Unit is a label
m2 lets you filter. The gap formula never reads the unit. Do not add a square-metre gap to a linear-metre gap and call it one quantity. Filter Unit first.
Text quantities do not subtract
If Measured was pasted as text, E2-D2 will not be 20. Re-enter 120 and 100 as numbers. Then read the window again.
Distinct from a site percent and a budget line
Daily site progress uses a date and a percent. Routing time uses minutes. An event budget uses planned money and money spent. Do not paste those rows here and expect this sum to mean the same thing.