How to normalize data to a 0-1 scale
✓ Verified in LibreOffice 25.8.7.3Min-max scaling — put different-ranged metrics on one comparable footing.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =(A2-MIN($A$2:$A$100))/(MAX($A$2:$A$100)-MIN($A$2:$A$100)) | Anchor the range and fill down. The minimum maps to 0, the maximum to 1. |
| Google Sheets | =(A2-MIN($A$2:$A$100))/(MAX($A$2:$A$100)-MIN($A$2:$A$100)) | Identical. |
| LibreOffice Calc | =(A2-MIN($A$2:$A$100))/(MAX($A$2:$A$100)-MIN($A$2:$A$100)) | Identical. |
How it works
Subtracting the minimum shifts the scale to start at zero; dividing by the range squeezes it to end at one: 30 sits exactly halfway between 10 and 50 → 0.5. Useful before averaging unlike metrics (a 0-100 score with a 0-5 rating) or for progress bars. Two caveats: a constant column gives 0/0 (guard with IF(MAX=MIN,...)), and new data outside the original min/max lands outside 0-1 — recompute or clamp. For outlier-heavy data, the z-score recipe is the sturdier scaling.
Verified, not just documented
We ran =(A3-MIN(A2:A4))/(MAX(A2:A4)-MIN(A2:A4)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 0.5 — exactly the expected result. Every formula here is confirmed by actually executing it.