kN summed with N in Excel™: the factor-of-1000 error
A load schedule has one load in kN and another in N. Someone sums the column. Excel™ returns a number, and that number is out by a factor of a thousand for part of the total. (An illustrative case; the figures depend on your schedule.)
Why it is easy to miss
Both values are forces, so the formula is dimensionally fine. Only the prefix differs. A check that asks "can these be added?" says yes. A check that asks "are they in the same unit?" says no.
Prevent it
These steps apply to Excel™ in Microsoft 365.
- Decide one unit per column and say so in the header, such as "Load (kN)".
- Convert on entry. For pasted data in a different unit, convert it in a helper column, for example
=B3/1000for N to kN with the factor named in the header, and use the helper column in totals. - Show the unit in the cell with a custom number format:
0.00" kN". - Never type a bare 1000 or 0.001 into a formula without a note saying what it converts.
Find it by hand
- Read down the column headers and units rows for any column that contains more than one unit.
- Search the sheet for cells whose number format or note names a different unit from their column. Ctrl+F can find "kN" in values and, with Look in set to Notes, in cell notes. Search each separately.
- For a total you doubt, recompute it with every item converted.
Report it at the cell
Unit-aware Calculation Checker flags the same quantity written in two prefixes as a contradiction (UN-02) and points to both cells, with the evidence under the finding. The rule for two quantities that cannot legally be added (UN-01) is separate. It reports a count of contradictions, with no score. Checking the active sheet is free.
See Unit-aware Calculation Checker for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Unit-aware Calculation Checker is not affiliated with or endorsed by Microsoft.