Average Monthly Growth rate

[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!

 

[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%

 

[Jarek Kujawa]

sorry for mistake, should be:

"yr data starts in A5"

 

[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.

 

[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)))