Build a unit-conversion function library

Northwind ships globally, so its spreadsheets are full of small conversions — a supplier quotes kilograms, a client wants pounds; a route is in kilometres, a report wants miles. Each one is a quick search and a multiply, but done a dozen times a day it adds up, and a mistyped factor is easy to miss.

This is a custom function. One =UNIT() formula converts a value from any supported unit to any other in the same family — length, mass, or volume — plus the one conversion that does not work by a simple factor, temperature. The conversion factors live in one table, so adding a new unit is a single line.

What you’ll need

• A sheet with values to convert in a column — say in column B, with a header in row 1.
• A spare column for the converted result.
• Nothing else. A custom function runs as a formula straight from the bound script; there is no trigger and no setup.

The script

// Conversion factors to each family's base unit:
// length -> metres, mass -> grams, volume -> litres.
// Add a unit by adding one line in the right family.
const UNITS = {
  // length (to metres)
  m: 1, km: 1000, cm: 0.01, mi: 1609.34, ft: 0.3048, in: 0.0254,
  // mass (to grams)
  g: 1, kg: 1000, lb: 453.592, oz: 28.3495,
  // volume (to litres)
  l: 1, ml: 0.001, gal: 3.78541, qt: 0.946353,
};

// Which units belong to the same family. A conversion is only
// valid when both units appear in the same set.
const SETS = [
  ['m', 'km', 'cm', 'mi', 'ft', 'in'], // length
  ['g', 'kg', 'lb', 'oz'],             // mass
  ['l', 'ml', 'gal', 'qt'],            // volume
];

/**
 * Converts a value from one unit to another within the same
 * family, with a special case for Celsius and Fahrenheit.
 *
 * @param {number} value The number to convert.
 * @param {string} from The unit to convert from, e.g. "kg".
 * @param {string} to The unit to convert to, e.g. "lb".
 * @return {number|string} The converted value, or an error marker.
 * @customfunction
 */
function UNIT(value, from, to) {
  // Guard: need a value and both units.
  if (!value || !from || !to) return '';

  const f = from.toLowerCase();
  const t = to.toLowerCase();

  // Temperature is not a simple multiply — handle it directly.
  if (f === 'c' && t === 'f') return value * 9 / 5 + 32;
  if (f === 'f' && t === 'c') return (value - 32) * 5 / 9;
  if (f === 'c' && t === 'c') return value;
  if (f === 'f' && t === 'f') return value;

  // Both units must live in the same family to convert.
  const set = SETS.find((s) => s.includes(f) && s.includes(t));
  if (!set) return '#UNSUPPORTED';

  // Convert via the family's base unit: into base, then out.
  return value * UNITS[f] / UNITS[t];
}

How it works

1. UNITS maps every supported unit to a factor expressed in its family’s base unit — metres for length, grams for mass, litres for volume. SETS records which units belong together.
2. UNIT returns an empty string if the value or either unit is missing, so a half-filled row stays blank rather than throwing.
3. Both unit names are lower-cased, so "KG", "Kg", and "kg" all work.
4. Temperature is handled first, because Celsius and Fahrenheit do not convert by a single factor — they need an offset as well as a scale.
5. For everything else, the function finds the family that contains both units. If no set holds both — say someone tries kilograms to miles — it returns #UNSUPPORTED instead of a meaningless number.
6. The conversion itself goes through the base unit: multiply by the from factor to reach base units, then divide by the to factor to land in the target unit.

Example run

Formula	Result	Why
=UNIT(5, "kg", "lb")	11.0231	5 × 1000 ÷ 453.592
=UNIT(100, "c", "f")	212	100 × 9/5 + 32
=UNIT(10, "km", "mi")	6.2137	10 × 1000 ÷ 1609.34
=UNIT(2, "gal", "l")	7.5708	2 × 3.78541 ÷ 1
=UNIT(3, "kg", "mi")	#UNSUPPORTED	mass and length are different families

Use it

Type the formula into the first result cell, pointing at the value and naming the two units in quotes:

=UNIT(B2, "kg", "lb")
=UNIT(B2, "c", "f")
=UNIT(B2, "km", "mi")

Then drag it down the column. Each row converts on its own, and adding a unit to UNITS and SETS makes it available everywhere at once.

Watch out for

• A conversion only works inside one family. Mass to length, or volume to mass, returns #UNSUPPORTED — there is no density assumed.
• Temperature is hard-coded for Celsius and Fahrenheit only. Kelvin, or any other scale, needs another explicit case; it cannot live in the factor table.
• Unit names must match the keys in UNITS exactly once lower-cased. A typo like "kgs" or "miles" falls through to #UNSUPPORTED.
• The result is unrounded. For a fixed number of decimal places, wrap it: =ROUND(UNIT(B2, "kg", "lb"), 2).
• UNIT expects a single value, not a range. =UNIT(B2:B9, "kg", "lb") will not convert the column — apply it one row at a time and fill down.