On Thu, 11 Oct 2007 22:29:03 -0700, Fenil Shah
wrote:

Hi,

I need help real bad and pronto. Need to calculate average annual growth
rate using geomean. My array has negative nos. as can be seen:
A1: 2.1
A2: 3.8
A3: 2.1
A4: -4.1
A5: -0.8
A6: 4.6
A7: 0.6
A8: -1.7
A9: 4.1
A10: 4.1
A11: 4.5

i used the following formulae as posted on this site by David, harlan and
Ron:

=SUMPRODUCT(GEOMEAN(A1:A3+1))


=GEOMEAN(Range_of_Percentages+1)-1 (DAVID)

or

=EXP(AVERAGE(LN(Range_of_Percentages+1)))-1 (HARLAN)

=GEOMEAN(1+K1:K3)-1 (RON)

(array-entered)

It doesn't work!!! Can somebody pl. help me? My work is being held up for
this one thing.

Fenil.




Most likely, your values are not percentages, but rather the numeric
representations of the percentages multiplied by 100.

If that assumption is correct, then to compute the GEOMEAN, you should use the
following **array-entered** formula.

To **array-enter** a formula, after entering the formula in the cell or formula
bar, hold down <ctrl<shift while you hit <enter. If you did it correctly,
Excel will place braces {...} around the formula:

=GEOMEAN(A1:A11/100+1)-1

To convert it to your (x 100) format, multiply the result by 100. Again, be
sure to **array-enter** the formula.

e.g. =100*(GEOMEAN(A1:A11/100+1)-1)


With your data, I get a result of 1.716%



--ron