Percentages in two scales
A rate is written as a fraction of one in some rows and as a number out of a hundred in others, so 0.15 and 15 mean the same thing.
What it looks like
Before
| discount_rate |
|---|
| 0.15 |
| 15 |
| 0.2 |
| 20 |
| 1 |
After
| discount_rate |
|---|
| 0.15 |
| 0.15 |
| 0.20 |
| 0.20 |
| needs a decision |
How it happens
- A spreadsheet stored the rate as a fraction and displayed it out of a hundred, and some rows were exported one way and some the other.
- People typed the rate the way they say it, while a system wrote it the way it calculates with it.
- Two sources with different conventions were appended into one column.
What it breaks
- Any calculation using the rate is wrong by a factor of a hundred on the affected rows.
- Averages are dominated by the rows on the larger scale.
- Validation that allows both scales passes everything, so the error goes unnoticed.
How to spot it
- Sort the column. Two scales usually form two clusters, one below one and one well above it.
- Check the largest and smallest values against what is plausible for this rate.
- Compare by source file or entry date. The scale often switches cleanly at a boundary.
How to fix it
- Choose one scale and state it in the column name or its documentation.
- Convert by source where the source is known, since that is stronger evidence than the size of the value.
- Do not convert values that are plausible on either scale, such as a value of exactly one or a small fraction of a small rate. Those need a decision.
- Mixed unitsValues in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
- Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
- Outliers from entry errorsA value sits far outside the rest because of a slipped key, an extra zero, or a misplaced decimal point.
TermsUnit of measure, Normalization, Validation, Consistency