AppGarden Unit-aware Calculation Checker logo AppGarden

Unit-aware Calculation Checker

kN summed with N in Excel™: the factor-of-1000 error

By Paul Kemp · Published

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.

  1. Decide one unit per column and say so in the header, such as "Load (kN)".
  2. Convert on entry. For pasted data in a different unit, convert it in a helper column, for example =B3/1000 for N to kN with the factor named in the header, and use the helper column in totals.
  3. Show the unit in the cell with a custom number format: 0.00" kN".
  4. Never type a bare 1000 or 0.001 into a formula without a note saying what it converts.

Find it by hand

  1. Read down the column headers and units rows for any column that contains more than one unit.
  2. 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.
  3. 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.