On Oct 12, 3:45 am, Fenil Shah
wrote:
Thank you so much for your quick response. But none of the formulae you have
suggested have worked. I am getting the value error for all of them. The
array that I have presented is annual percentage changes or what u call the
annual growth rates, calculated using the formula =((y1/y0)-1)*100. I now am
trying to calculate the average annual growth rate using the geomean. Any
further suggestions?

Well, my first suggestion is that you change the formula above.
Remove "*100" and simply choose the Percentage numeric format.

Having said that, the first formula that I provided should work with
your data as you describe them, namely:

=100*(geomean(1+A1:A11/100)-1)

I had tested that with the numbers that you posted.

My guess is that you did not enter the formula correctly. Either you
did not cut-and-paste it correctly (modifying A1:A11 appropriately),
or you did not "commit" the formula by typing ctrl-shift-Enter.

Assuming the latter, try the following: select the cell, press F2,
then press ctrl-shift-Enter.

Please confirm that you know what an array formula is and how to enter
it.

It would help if you would explain what you mean by "does not work".
What exactly is the result?

One final comment.... You say that you computed the percentages by y1/
y0, y2/y1, etc (then subtracting 1 and multiplying by 100). If your
spreadsheet still has the original data, y0 through yN, you can
compute the geometric mean directly by the following formula
(multiplied by 100, as you did, which I do not recommend):

=100*( (yN/y0)^(1/N) - 1 )

Note that that is __not__ an array formula.

HTH.