Engineering calculations in Excel: where it works and where it fails

Why most calculations live in spreadsheets, what goes wrong there, and when to move them out.

Ask an engineering office where its calculations are and the answer is a folder of spreadsheets: a beam check someone built in 2011, a pressure-drop sheet with three generations of copies, a pump-sizing workbook that only one person dares to change. Excel is on every computer, needs no training and does arithmetic correctly, and that is why it won. It is also the tool in which the most expensive calculation errors have been made, and the one in which they are hardest to find. This page is about why, what you can do inside Excel, and when the calculation should live somewhere else.

What goes wrong

Units are not there

A cell holds a number. Whether it is in inches or millimetres, psi or MPa, is in the header, in a comment, or in the author's head. Conversion factors are typed into formulas (× 0.0254, / 145.04) where they are invisible and easy to double up or leave out. A unit error is the most common serious mistake in hand calculations, and Excel cannot detect it, because it does not know the units exist.

The formula is hidden behind the result

What you see is 3.46; what computed it is =B12*C7^2/(8*$F$3), which you see only if you click the cell, and which refers to other cells by their coordinates, not by what they are. Reviewing a spreadsheet means clicking through every cell, and almost nobody does. Audits of spreadsheets in use regularly find errors in a large share of them; the usual estimate is that something is wrong in most spreadsheets of any size.

Copies drift

The sheet is copied for a new project, modified, copied again. Within a year there are five versions with different fixes, and the one on the server is not the one that was used. Which version a report came from cannot be told from the report.

Iteration and solving are awkward

An implicit equation (the Colebrook friction factor, a cable sag, a trial section) needs Goal Seek, which is manual and does not rerun when an input changes, or circular references with iterative calculation turned on, which quietly settle on wrong values when they fail to converge.

The explanation is somewhere else

A calculation is an argument: assumptions, method, result, check. A spreadsheet holds the numbers; the argument is in a Word document or in nobody's memory, and when the two disagree there is no way to tell which was updated last.

Doing it better in Excel

If the calculation is going to stay in Excel, these habits remove most of the risk:

Name every input and intermediate. Named ranges make =W*L^2/(8*Z) readable where =B12*C7^2/(8*F3) is not.
One unit system per workbook, written in the header of every column, and all conversions in one visible block of cells, never inside a formula.
Inputs, calculations, outputs in separate areas, with inputs colour-coded and protected cells everywhere else.
A check column: a hand-calculated value next to the formula result for one known case, flagged if they differ.
No merged cells, no hidden rows, no hard-coded numbers in formulas. Use Show Formulas (Ctrl + `) and Trace Precedents when reviewing.
A version cell and a change log in the workbook, and a template that is read-only on the server.
For solving, a visible iteration table (a column of successive guesses) rather than circular references, so that convergence can be seen.

Done well, an Excel calculation is reviewable. Done well takes more discipline than most offices have time for, and a new person inherits a sheet, not the discipline.

When to move it out

A calculation worksheet (Mathcad, Swift Calcs, SMath and their kind) fixes the problems above by design rather than by habit: the formula is what you see, with the result next to it; every value carries its unit and a mismatch is an error on the line; text, figure and formula are in one document that is the report; solvers rerun when an input changes; and a sheet can include another sheet's functions, so a library is a reference, not a copy. Moving makes sense when:

the calculation will be reviewed, by a checker, a client or an authority, and the review has to be done from the document;
units are mixed, as in anything that takes catalogue data in one system and code requirements in another;
the same calculation is reused across projects and the drift between copies has already bitten;
the sheet needs to be shared with people who should not be able to change it, or who do not have the program;
there is an implicit equation, a fit or an integration at the centre of it.

Excel stays the better tool for tables of data, for schedules, for cost and quantity take-offs, for anything that is mostly rows and columns, and for the quick one-off that will never be seen again. The boundary is simple to state: if someone else has to trust the result, the formula should be visible and the units checked, and that is what a calculation worksheet does and a spreadsheet does not.

A side-by-side

The pipe pressure drop guide is a good test case. In Excel the Colebrook equation needs Goal Seek or a circular reference, the roughness and diameter must be in the same unit by convention, and the Reynolds number formula hides whether the viscosity is dynamic or kinematic. In the worksheet at the end of that page the equation is solved by a numeric solver that reruns on every change, the units are declared on each input, and the whole calculation reads top to bottom. Press "Try it live" on it and change the flow; that is the difference in one minute.

Other guides

Pipe pressure drop calculation with Darcy–WeisbachFriction factor, head loss and pressure drop for a given flow, with the Colebrook equation solved numerically.Lead screw torque and motor sizingFrom the load to push and the screw lead to the torque, speed and power the motor must deliver.Bolt preload and tightening torqueProof load, preload, the torque–tension relation, and the margin left for the external load.Beam deflection and bending stressMoment of inertia, section modulus, and the deflection and stress formulas for the common load cases.Pump affinity laws: flow, head and power when speed or size changesWhat a change of speed or impeller diameter does to flow, head and power, and when the scaling holds.Heat exchanger sizing with the LMTD methodDuty, log mean temperature difference, overall coefficient and the area that follows.Snap-fit design: deflection, strain and mating forceStrain at the undercut, deflection force, and the push-on and pull-off forces from the angles and friction.Lifting lug and padeye designDesign load, pin bearing and tear-out, bending at the base and the von Mises check, with DNV factors.Natural frequency of a rectangular plateFlexural rigidity, mass per area and the frequency coefficient for the support condition.Gear geometry: module, pitch diameter and bevel gear dimensionsFrom module and tooth count to every diameter, the centre distance, and the cone geometry of a bevel pair.