1
$\begingroup$

Why is my overall delta percentage of 0.9% (bottom right) less than all 5 of the values that make it up (1.7%, 3.2%, ...)? I know with the difference in magnitude of each row and column that it could skew the percentages, and I know that I'm subtracting percentages, but I'd still expect at least 1 of the values in the right-most column to be lower than 0.9%!

      A        B    B/A            C        D    D/C       D/C - B/A  77,629   74,645   96.2%       7,986    7,814   97.8%        1.7%  59,496   55,606   93.5%       9,779    9,448   96.6%        3.2%  48,764   40,484   83.0%      11,413   10,320   90.4%        7.4%  25,685   18,777   73.1%       9,901    7,882   79.6%        6.5%   2,915    1,956   67.1%       1,165      822   70.6%        3.5%  214,489  191,468   89.3%      40,244   36,286   90.2%        0.9%  Totals 
  • 0
    Very cool, Rasmus. I figured I wasn't the first to see this, so it's great to see those real world examples.2011-03-09

1 Answers 1

3

Here's a simpler example that makes it clear how this can happen:

     A      B    B/A          C      D   D/C     D/C-B/A        10000   9000    90%        100     95   95%        5%            100     86    86%      10000   9100   91%        5%           10100   9086    90%      10100   9195   91%        1%  Totals 

Your "Totals" row is not a fair average of the other rows at all; when you just look at the percentages, you lose all information about which rows have big numbers and which rows have small numbers. In your example, the upper rows for A and B are comparatively more significant, which skews the total B/A percentage up, while the lower rows for C and D are comparatively more significant, skewing the total D/C percentage down.


If you really want to measure some kind of average of the difference of the percentages among all five rows, you have several options-- you could just take the average of your rightmost column, or you could take a weighted average (weighted by A or C, maybe). The best choice depends on what the data means and on precisely what you want to measure.