Rate of Change

A rate of change expresses a difference relative to a base: subtract the earlier value from the later one and divide by the earlier one. The result is a ratio, not a percentage — 0.2 means twenty percent, and percent is a display format applied afterward. In Excel, an existing stored value of 0.2 displays as 20% with Percentage format. An existing stored value of 20 displays as 2000% under that format. Check the stored calculation as well as the display: applying a number format again does not itself multiply the stored value.

Which value serves as the base decides the number, and reversing it does not simply reverse the sign. Going from 100 to 120 is a gain of 0.2 of the original; going from 120 back to 100 is a loss of about 0.167 of the new starting point. Quoting a growth figure without naming its base period is therefore not a rounding issue but an ambiguity, and comparisons across periods only hold when every figure uses the same kind of base.

The same reasoning governs proportions, where the mistake is more consequential because a raw count looks like a finding. Twenty low-performing items out of 125 is 16%; the same twenty out of 80 is 25%. Whenever groups differ in size, a count measures group size as much as the thing being counted, so the group with the most rows will tend to lead on any count. Dividing by each group’s total is what makes them comparable.

Two habits keep these figures honest. State the denominator alongside the rate, since a proportion is only as well defined as what it counted — rows with missing values are not zeros, and whether they belong in the denominator is a decision to record rather than to leave to omission. And round at the point of reporting rather than at each intermediate step, so the displayed figure does not disagree with the stored one.

References:

• Format numbers as percentages
• ROUND function