0

Calculate percentage change over a period of time.

How would I go about calculating the percentage change over time taking the values from B12, C12, D12, E12, F12, G12, and H12? I'd like to see the % increase over that 7 month time span.

I'd also like to calculate percentage change from just the values in B12 and H12 not taking into account the values from C12, D12, E12, F12, and G12 into account.

I'd like to apologize in advanced for not being able to refer to the two different growth rates I'd like to calculate with the correct terminology.

1

Why not just use the two extreme values, the growth from B12 to H12?

Ignore the rest inside, unless I'm misunderstanding the question.

  • Just thinking out loud... Isn't it more accurate when you take all values from first month to the last month rather than just B12 and H12? – Gregg Hawkins May 21 '12 at 18:12
  • @GreggHawkins - Why do you feel like that would be true? How is the CTR calculated? If you wanted to do that just calculate the sum of ( C12 - B12 ) + ( D12 - C12 ) + ( E12 - D12 ) + ( F12 - E12 ) + ( G12 - E12 ) + ( H12 - G12 ) this would be the total growth between B12 and H12 – Ramhound May 21 '12 at 18:52
  • Thanks Ramhound. CTR is calculated from other spreadsheets where I took the average of all values in November 2011. – Gregg Hawkins May 21 '12 at 19:14
1

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.

Example

Setup: 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, E9=D9*$E$8, 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:

  1. The goal cell J9. (This must be a formula depending on E8, the growth factor aka "the changing cell")
  2. Your goal value you want or did attain (J9 to become 5.19 in your case)
  3. 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%.

  • Great first answer! Have a look at how to include images and do more formatting for code, seems you already figured out how to do some formatting. – Jens Erat Jan 24 '13 at 21:50
0

Best way to determine month to month growth over a few periods is to take the average growth of all the percentages. It's a crude look, but it can be rather accurate. If you would want to know the total growth, you would have to take the variance from the most recent month and the beginning month, and taking that number divided by the beginning month. That will give you total growth. I hope I understood your question right! LOL

  • Yeah it all makes sense. Thanks guys for the responses. I'll just take =(H12/B12)/B12 right? – Gregg Hawkins May 21 '12 at 18:32
  • For total growth? Or Average growth? For Average you could take =Sum(B12:H12)/Count(B12:H12), which is the same as doing =AVERAGE(B12:H12). Which would be 3.995714% per month. For total growth, you'd have to use the actual numbers, not the percentages. Good Luck! – Brian May 21 '12 at 18:40
  • Ahh makes sense now. Yeha, I'm looking for total growth... Thanks a bunch! – Gregg Hawkins May 21 '12 at 19:14
  • To add onto my previous response--I'm looking for total growth... I guess what I'm trying to show is that when I took over the the accounts in November 2011 and CTR was 1.5% (B12) how much % increase (growth) in CTR there was up until May, which is now 5.19% (H12). I'm probably over thinking this way too much... Thanks a bunch! I appreciate the quick responses and help from everyone so far! – Gregg Hawkins May 21 '12 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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