The proper method to use is 'Goal Seek.' In plain terms, Goal Seek tells you the grade or steepness of the change from the beginning to the end. It doesn't rely on the numbers in between but just on how many steps it takes to get from A to B. If you go from 1 to 2 in one step, that's 100% fast growth. Now if you go from 1 to 2 over 10 steps that's 7.2% slow growth.
You'll need to create a set of data which grows for the similar period of time (steps). It will start with the same 1.5 start CTR value (
D9=1.5). The growth of this set can be 1.0 to start (
E8=1.0). Doesn't matter as it is going to change.
Each new data point in the set is the previous data point multiplied by our growth factor of 1.0 (1.0 will give no growth, btw). Therefore,
F9=E9*$E$8, etc. pp. All the way until
J9 (May 2012).
Now go to tab DATA > What-if Analysis > Goal Seek. There are three things required:
- The goal cell J9. (This must be a formula depending on E8, the growth factor aka "the changing cell")
- Your goal value you want or did attain (J9 to become 5.19 in your case)
- The cell which will change so that we can reach our goal of 5.19, i.e., the changing cell, growth factor, E8.
Once you have these three values hit "Ok".
Goal seek will calculate the growth value it takes to go from 1.5 to 5.19 over the course of 6 months (or steps) 23%.