This might seem (and be) a really trivial question!

I have this table:

id until_2000 after_2000
1  1          1          1
2  2          1          2
3  3          2          1

Now I want to compare the relative change between the columns after_2000 and until_2000. Just dividing the after_2000 values by the until_2000 (and substract 1?!) values yields the colum rel_change

  id until_2000 after_2000 rel_change
1  1          1          1        0.0
2  2          1          2        1.0
3  3          2          1       -0.5

While the first row stays as is (has a change of 0%), the second row doubles (has a relative change of 100 %). Yet the third row is half the value and thus has a relative value of -50 %.

Now my trivial question is how to correctly compute and average these relative changes. In my line of thinking the values of 0, doubling, and "halfing" should result in an average change of 0.

Yet adding 0 + 1 - 0.5 and dividing it by three yields a value of 0.16666. What am I conceptually understanding wrong here?


4 Answers 4


The problem is twofold:

You shouldn't subtract one. Just divide one by the other, and you'll get relative numbers 1, 2, 0.5 -- equivalent to 100 % (no change), 200 % (double) and 50 % (half.)

Then to get the average relative change, you take the geometric mean: multiply the relative changes and take the root. $\sqrt[3]{1 × 2 × 0.5} = 1$, leaving the value unchanged after all three steps.

  • $\begingroup$ oh great thanks so much! Why is it that I need to multiply these values to get the average ? $\endgroup$
    – Lenn
    Jul 17, 2022 at 18:00
  • 4
    $\begingroup$ en.wikipedia.org/wiki/Geometric_mean $\endgroup$ Jul 17, 2022 at 18:07
  • 1
    $\begingroup$ @Lenn obviously be careful with dividing if it's by 0. I'm presume the "after_2000" could be 0. $\endgroup$
    – Sam Dean
    Jul 18, 2022 at 9:56
  • $\begingroup$ There is a big question on how would you want a change from 100 to 200 to modify the result, If it's also a +100% then just count as 2 or if it should have more weight because the untill_2000 is bigger. I added a answer on this point of view $\endgroup$
    – el Josso
    Jul 18, 2022 at 13:33
  • $\begingroup$ But 3.5 divided by 3 still does not give 1, how do we resolve that point? What is the way to average these numbers? $\endgroup$
    – xojfqa
    Jul 18, 2022 at 22:09

As good as is kqr answer, I would go for another method, which would work even if you have a 0 value somewhere and would keep his way of calculating the relative change

When you ask the question "how to correctly compute and average these relative changes" we could also first do the average then compute the relative change. You would then have

id    until_2000 after_2000 rel_change
total       4          4        0    

Then you can calculate the difference as you were calculating before
The average and the sum of the different rows lead to the same result here as your denominator is the same for both (3 rows)

$\frac{sumAfter/nRows - sumUntil/nRows}{sumUntil/nRows} = \frac{sumAfter - sumUntil}{sumUntil}$


Adding the numbers and then dividing by the count is the arithmetic mean. When dealing with relative change, it often makes more sense to use the geometric mean, which is where you take the product of the numbers and then taking the nth root. So if you take 0.512 and then take the third root, you get 1, which makes more sense as the "average" change. If you had 0.5, 1, 2, and 2, then you'd take the fourth root of 2, which is 1.189

You can also take the logs of the ratio. You can then take the arithmetic mean of the logs. If you want to convert back to relative change, you can then take the exponential of the result. Taking the log, then the arithmetic mean, then taking the exponential of the result gives you the same answer as the geometric mean.


Alternatively you can use log returns (there are issues with division by 0, same as multiplication by 0 with a geometric mean).

> df=structure(list(id = 1:3, until_2000 = c(1L, 1L, 2L), after_2000 = c(1L, 
2L, 1L)), class = "data.frame", row.names = c("1", "2", "3"))

> log(df$after_2000/df$until_2000)
[1]  0.0000000  0.6931472 -0.6931472

and you can use a regular average now (which is 0 in this case).
Adding another row

> df=rbind(df,c(4,3,5))
> log(df$after_2000/df$until_2000)
[1]  0.0000000  0.6931472 -0.6931472  0.5108256

the regular average of these 4 log values is $0.1277064$, an overall $\approx 12.7 \%$ increase.

Compare this to kqr's method:
Edit after the correct formula for the geometric mean was written down

> prod(df$after_2000/df$until_2000)^(1/nrow(df))
[1] 1.136219

so an average $13.6 \%$ increase.

  • 1
    $\begingroup$ I'm pretty sure you're misunderstanding the meaning of this calculation. I don't quite follow it, but averaging the logs of anything won't give you a percentage. Perhaps you could explain what you're doing in your answer? $\endgroup$
    – wizzwizz4
    Jul 18, 2022 at 15:59
  • 1
    $\begingroup$ @wizzwizz4 Log returns can be interpreted as percentage differences (assuming small enough differences), there is a lot going on here, see What does the average log-return value of a stock mean? and Why stock prices are lognormal but stock returns are normal. $\endgroup$ Jul 19, 2022 at 6:14
  • $\begingroup$ For small values, yes… but you have access to a computer! This kind of approximation is silly. 12.7% and 13.6% are quite far apart. $\endgroup$
    – wizzwizz4
    Jul 19, 2022 at 10:27
  • $\begingroup$ @wizzwizz4 It's not an approximation, it approximates percentage changes under certain conditions, however it still measures something different, so it's understandable that it is different. Whether this makes sense is context dependent. $\endgroup$ Jul 19, 2022 at 14:01
  • $\begingroup$ I see! It might be worth explaining that in your answer, for stats noobs like me. $\endgroup$
    – wizzwizz4
    Jul 19, 2022 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.