Average Monthly Growth rate

P

Plum

Hi there,
I am trying to calculate the average monthly growth rate for the following
data for example:
Jan-08 Feb-08 Mar-08 Apr-08 May-08
9,203 9,520 8,078 8,744 8,070
The answer is 6.27% based on calculating it like this, putting an =AVERAGE
formula to return 6.27%:
Jan-08 Feb-08 Mar-08 Apr-08 May-08
9,203 9,520 8,078 8,744 8,070
42.53% 3.44% -15.15% 8.24% -7.71% 6.27%
Surely there is a simple excel formula to do this so it doesn't have to be
done in two lines?
Any help greatly appreciated!
 
J

Jarek Kujawa

presuming yr data (=9,203) starts in A1 with the use of the following
formula:

=(B5-$A$5)/(B5*COLUMN(B5)-COLUMN($A$5))

i achieved the following results for the AVERAGE MONTHLY growth rate

9203 9520 8078 8744 8070
1,67% -4,64% -1,31% -2,81%
 
P

Plum

Hi Jarek,
thanks yes that works, but I'm actually trying to do it all in one row such
that the second row is not necessary. Surely there is a formula that will do
this - it is quite similar to IRR or RATE, does anyone know?
Regards.
 
J

Jarek Kujawa

would this help?

=(OFFSET($A$5,,COUNTA($A$5:$I$5)-1)-$A$5)/(OFFSET($A
$5,,ILE.NIEPUSTYCH($A$5:$I$5)-1)*(COUNTA($A$5:$I$5)-COLUMN($A$5)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top