Relative comparison of two data sets?

[sbolt6]

Hello Everyone,

I am trying to figure out a way to determine/show the relative improvement of a productivity KPI compared to the increased travel volume on the roads. I have attached a simple example of the type of data I would like to compare. The issue that I am trying to present is that our productivity numbers have not changed significantly year over year but compared to the increased traffic volume we have to deal with on the roads now, it appears we are doing better than it appears simply by looking at the productivity alone.

Any ideas on how to represent this in relative terms would be much appreciated.

Regards,

Scott

 

[Peter Bartholomew]

What is missing is a model of "all things being equal how would you expect the productivity KPI to depend on traffic volume. If you had reason to expect an inverse relationship (say) then the year on year change would be the product of the performance gain and the traffic density change. You could then subtract one and convert the result to a percentage.

You do need a model of traffic flow versus density as one heads towards gridlock. Such models are not within my area of knowledge.

 

[bosco_yip]

Try to calculate the "Average Annual Growth Rate" based on the data.



In H7, enter formula and copied down :

=(G7/C7)^(1/COUNT(C$6:G$6))-1

Then, based on the resulted rate, the Productivity compared with Traffic volume shown in the ratio of around 1:6.5

Regards
Bosco

 

[sbolt6]

Thank you both again for your incite and expertise. Much appreciated!!! Take care.

Scott