Construction Formulas — Excel Formulas for Construction Work
A library of Excel formulas combining built-in Excel functions + DVDAddin UDFs to solve problems specific to the construction industry.
Type UDF names exactly as documented
Every add-in function starts with the dvd prefix (for example dvdVnd, dvdSteelWeight). The full list of 47 functions is on UDF functions — if you type a name that is not on that list, Excel returns #NAME?.
Quantities & Take-off
Total quantity by work type
=SUMIFS(KhoiLuong, LoaiCongTac, "Bê tông", HangMuc, "Móng")Or use a pivot table for a project-wide overview.
Unit conversion
| Formula | Description |
|---|---|
=A1*1000 | m → mm |
=A1/1000 | mm → m |
=A1*0.001 | g → kg |
=A1*1.4*100 | m³ of concrete → kg of cement (grade 250) |
=A1*7850 | m³ of steel → kg (density 7850 kg/m³) |
Counting rebar by diameter group
=COUNTIF(DuongKinh, "D16") + COUNTIF(DuongKinh, "Φ16")Combines both the D and Φ notations (Vietnam uses both).
Anchorage length of rebar (anchor length)
=IF(MacBeTong<=20, 40*DuongKinh, 30*DuongKinh)Or use IFS (Excel 2019 and later):
=IFS(MacBeTong<=20, 40*D, MacBeTong<=30, 35*D, TRUE, 30*D)Concrete
Concrete mix design (grade 250, slump 8±2)
| Material | Norm (per 1 m³) |
|---|---|
| PCB30 cement | =350 (kg) |
| Coarse sand | =0.45 (m³) |
| Aggregate 1×2 | =0.85 (m³) |
| Water | =185 (liters) |
| Admixture (if any) | =350*0.005 (kg) — 0.5% of the cement |
Multiply by the volume of concrete to be poured → the material quantities.
Concrete strength by age (US, ACI 209)
=CuongDo28Ngay * Tuoi / (4 + 0.85*Tuoi)E.g. the strength of M250 at 7 days:
=250 * 7 / (4 + 0.85*7) = 250 * 7 / 9.95 ≈ 176 kg/cm²Concrete volume of a bored pile
=PI()/4 * (DuongKinh/1000)^2 * ChieuDaiE.g. a D800 pile, 30 m long:
=3.14159/4 * 0.8^2 * 30 = 15.08 m³Rebar
Weight of 1 m of round bar
=PI()/4 * (D/1000)^2 * 7850Or the quick mental shortcut:
=D*D / 162.2 (result in kg/m, D = mm)| D (mm) | Weight (kg/m) |
|---|---|
| 6 | 0.222 |
| 8 | 0.395 |
| 10 | 0.617 |
| 12 | 0.888 |
| 14 | 1.208 |
| 16 | 1.578 |
| 18 | 2.000 |
| 20 | 2.466 |
| 22 | 2.984 |
| 25 | 3.853 |
| 28 | 4.834 |
| 32 | 6.313 |
Rebar weight by number of bars — dvdSteelWeight
The add-in function goes straight to kilograms for N commercial bars of 11.7 m, so you never have to remember the kg/m table:
=dvdSteelWeight(diameter, quantity)
=dvdSteelWeight(20, 84) → 84 D20 bars of 11.7 m, result in kg
=dvdSteelWeight(16, 30) → 30 D16 barsdiameter— the nominal diameter (mm).quantity— the number of bars when D ≥ 10. For coiled steel with D ≤ 8 this argument is read as the coil weight (kg) and the function returns that same value, because coiled steel is sold by weight, not by the bar.
→ Details: dvdSteelWeight
Total rebar weight from a bar schedule
=SUMPRODUCT(SoLuong, ChieuDai, KhoiLuong1m) / 1000Output: tonnes (kg → tonnes / 1000).
Linear interpolation in lookup tables — dvdNoiSuy
Norm tables, strength tables, span-coefficient tables… every one of them only gives a few discrete points. Instead of building a FORECAST/TREND formula every time, use:
=dvdNoiSuy(x1, x2, y1, y2, xnew)
=dvdNoiSuy(6, 9, 1.15, 1.32, 7.5) → the coefficient for a 7.5 m span→ Details: dvdNoiSuy
Number of stirrups for one beam
=ROUNDUP((ChieuDaiDam - 2*KhoangCachBaoVe) / KhoangCachDai, 0) + 1E.g. a 6 m beam, 5 cm cover, stirrups @200:
=ROUNDUP((6000 - 100) / 200, 0) + 1 = 31 stirrupsConstruction schedule
Number of working days (excluding Sundays)
=NETWORKDAYS.INTL(NgayBatDau, NgayKetThuc, 11)11 = the "Sunday is the only non-working day" mode.
1 (default) = Saturday + Sunday are non-working days.
To exclude Vietnamese public holidays as well, pass a fourth argument pointing at the range that holds the holiday list. The Gantt Tpl template already ships with that list, declared as the named range DSNgayLe (pointing at column A of the Input sheet):
=NETWORKDAYS.INTL(NgayBatDau, NgayKetThuc, 11, DSNgayLe)The Input sheet also carries a lunar–solar calendar cross-reference table so you can update Tết every year, and the named range LichLamViec (cell Input!B2) holds the weekly calendar mask: 7 characters, Monday → Sunday, Y = working, N = non-working — the default is YYYYYYN (Sunday off only).
These two named ranges are exactly what the add-in's Auto Calc (Tự động tính) command reads to run the schedule, and Setup (Cài đặt) writes back to them when you change the calendar in the dialog — so your schedule and your NETWORKDAYS.INTL formulas always share one calendar.
Number of calendar days
=NgayKetThuc - NgayBatDau + 1Finish date from start date + duration
=WORKDAY.INTL(NgayBatDau, Duration-1, 11)-1 because NgayBatDau itself counts as one working day.
% complete as of today
=MIN(MAX((TODAY() - NgayBatDau) / (NgayKetThuc - NgayBatDau), 0), 1)Capped to [0, 1] so the result is never negative or above 100%.
Critical path and earliest start dates — do not build your own formulas
Computing CPM with plain Excel formulas is an iterative problem: every task has to wait for all of its predecessors, so a single-level VLOOKUP is only correct on a simple network and breaks as soon as there are SS/FF relationships or multiple predecessors.
The add-in already has a scheduling engine: turn on Auto Calc (Tự động tính) on the DVD Cons tab, declare Predecessors as Activity ID + relationship code (MHC1060FS, MHC1090SS, MHC1150FF), and the add-in recalculates Start / Finish every time you change a Duration or a date. The Total Float / Free Float columns in the Gantt template tell you which tasks are on the critical path (float = 0).
To check the quality of the network (dangling tasks, missing relationships, negative float, hard constraints…), use Health Check (Kiểm tra chất lượng) — the DCMA check set, around 12 metrics.
Cost & Estimating
Total contract value with 8% VAT (temporary rate, 2026)
=KhoiLuong * DonGia * 1.0810% VAT (traditional rate)
=KhoiLuong * DonGia * 1.10Extracting the pre-tax price from a VAT-inclusive price
=GiaCoVAT / 1.08 (VAT 8%)
=GiaCoVAT / 1.10 (VAT 10%)Amount in words — dvdVnd / dvdUsd
The "amount in words" line is mandatory in contracts, price certificates and payment minutes:
=dvdVnd(1500000)
→ Một triệu, năm trăm nghìn đồng.
=dvdVnd(1500000, 1)
→ Bằng chữ: Một triệu, năm trăm nghìn đồng.
=dvdUsd(C5)
→ spells the USD amount in cell C5 out in English words (dollars / cents)The second argument of dvdVnd takes only two values: 0 (default — the amount only) and 1 (adds the Bằng chữ: prefix).
Automatic currency conversion — DVDFx
DVDFx returns the exchange rate, not the converted amount — multiply it by the amount to get the result:
=DVDFx("USD","VND") → today's USD→VND rate
=A1 * DVDFx("USD","VND") → converts A1 (USD) into VND
=A1 * DVDFx("EUR","VND")
=DVDFx("USD","VND","2026-06-30") → the rate on a specific dateThe date argument accepts the yyyy-MM-dd or dd/MM/yyyy form; leave it out and today is used. The function runs asynchronously — the cell shows #N/A waiting... for a moment and then updates itself — and it needs an Internet connection.
Do not let a contract depend on a live exchange rate
Once the figures are final, copy the results and Paste Special → Values. If a live DVDFx formula stays in the contract value table, the numbers are different every time the file is opened.
→ Details: DVDFx
Price escalation by CPI
=GiaGoc * (1 + CPI/100)^SoNamTroiQuaE.g. a VND 100 million contract signed in 2020, escalated to 2026 prices at a CPI of 4%/year:
=100000000 * (1 + 0.04)^6 = 126,531,902 đMeasurement & Quantities
Room area from dimensions
=Dai * RongArea less doors/windows
=Dai * Rong - SUMPRODUCT(SoLuongCua, DienTich1Cua)Concrete volume of an isolated footing (box + blinding layer)
=A * B * H + A * B * 0.10.1 = the 10 cm blinding layer.
Concrete volume of rectangular columns
=A * B * H * SoLuongCotConcrete volume of a slab (less stair openings)
=DienTichSan * ChieuDaySan - SUMPRODUCT(DienTichLoTrong, ChieuDaySan)Data checking
Coloring cells with bad data (Conditional Formatting)
Set up the CF rule:
- Range:
B2:B100 - Formula:
=AND(ISNUMBER(B2), B2<0) - Format: red fill.
→ Cells holding a negative number turn red → data-entry errors are easy to spot.
Summing / counting by cell fill color
Quantity tables on site are often marked with colors (yellow = awaiting confirmation, green = accepted). Excel has no built-in function for this; the add-in does:
=dvdSumIfColor(F5:F200, $J$2) → sums the cells in F5:F200 whose fill color matches cell J2
=dvdCountIfColor(F5:F200, $J$2) → counts those cells→ Details: dvdSumIfColor · dvdCountIfColor
Conditional Formatting colors do not count
These two functions read the actual fill color of the cell. A cell colored by CF keeps its original fill color in Excel's object model, so it is not summed or counted.
Summing only the filtered rows
After AutoFiltering a large quantity table, SUM still adds the hidden rows:
=dvdSumVisible(F5:F2000)→ Details: dvdSumVisible
Counting blank rows in a range
=COUNTBLANK(A2:A1000)Highlight duplicate
CF → Highlight Cells Rules → Duplicate Values.
Or a formula:
=COUNTIF($A$2:$A$1000, A2) > 1Validating dates within a range
Data Validation:
- Allow: Date.
- Data: between.
- Start:
=DATE(2026,1,1), End:=DATE(2026,12,31).
→ The cell only accepts dates in 2026.
The DVDAddin UDFs most used in construction
A short list drawn from the 47 functions — see the full set on UDF functions.
| Function | Purpose |
|---|---|
dvdVnd(Number, [match_mode]) | Spells a VND amount out in words; match_mode=1 adds the Bằng chữ: prefix |
dvdUsd(Number) | Spells a USD amount out in English words |
DVDFx(from, to, [date]) | Foreign-exchange rate (real time or on a given date) — needs Internet |
dvdSteelWeight(diameter, quantity) | Rebar weight from the number of 11.7 m bars |
dvdNoiSuy(x1, x2, y1, y2, xnew) | Linear interpolation when reading norm tables |
dvdSumVisible(SumRng) | Sums only the visible cells (skips hidden / filtered rows) |
dvdSumIfColor(rngSum, rngCellColor) | Sums by cell fill color |
dvdCountIfColor(RangeToCount, ReferenceCell) | Counts cells by fill color |
dvdLookupAllSheets(lookupValue, lookup_column, result_column) | Looks a value up across every sheet in the workbook — suits one sheet per work package |
dvdXlookup(...) | XLOOKUP with built-in IfNotFound / IfError handling, usable on Excel 2016/2019 |
dvdTableLookup(LookupRowValue, LookupColumnValue, TableRange) | Two-way table lookup (row × column) — coefficient tables, mix-design tables |
dvdConcatIF(Delimiter, ConcatRange, [ConditionRange], [Condition]) | Joins a list of locations / members under a condition |
dvdExplain(RangeToExplain, [HeaderRange], [OptionValue], [DecimalPlaces]) | Generates a quantity breakdown string such as 3=1+2 for records submitted for approval |
dvdLDate(shortDate, [Location], [Language]) | Turns a date into a minutes-style text string ("ngày … tháng … năm …"), with optional place name and bilingual output |
dvdSymbol(value) | Inserts ☐ / ☑ / ☒ into inspection forms |
dvdTranslate(Text, FromLang, ToLang) | Translates through Google Translate right inside the cell — needs Internet |
dvdQR(Text, [charsetName]) | Generates a QR code (for acceptance sheets, member tags) |
dvdPic(ImgPath, [FitMode], [Space], [Extensions]) | Inserts a site photo into a cell from its file path |
Network and volatile functions slow the workbook down
dvdTranslate, dvdStock, DVDFx, dvdAIExplain call the Internet for each cell. dvdAutoHide, dvdMCLookup, dvdMVLookup, dvdSumVisible, dvdUniqueV are volatile functions and recalculate on every Excel recalc. On a quantity table of several thousand rows, finalize the figures and then Paste Special → Values.
Related
- UDF functions — the full list of DVDAddin's 47 functions.
- Full command reference — 186 ribbon commands, with every dialog field described.
- Sample Workbooks — the 6 bundled sample files, with their standard column layouts.
- Workflow: Construction cost estimating — the complete process.
- Recipes — advanced formula patterns.
- Glossary — construction terminology.