GEOMEAN

[Guest]

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.

 

[joeu2004]

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

Try the following array formula (commit with ctrl-shift-Enter):

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

I am presuming that your "percentages" are really the percentages
times 100. I think it would be better if you computed true
percentages, then selected the Percentage numeric format. Then, the
following array formula should work for you:

=geomean(1+A1:A11)-1

Occassionally, GEOMEAN() runs into trouble. The following array
formulas are more reliable:

=100*(exp(average(ln(1+A1:A11/100)))-1)

=exp(average(ln(1+A1:A11/100)))-1

HTH.

 

[David Biddulph]

"It doesn't work" is not the most helpful description of a problem. If you
want someone to help you, it would be wise to give more details.

What error message (if any) did you receive? What result did you get from
your input data, and what result did you expect?

The other think to bear in mind is that the formulae you are looking at were
obviously designed on the assumption that the inputs are ratios formatted as
percentage (or the ratio itself, as format doesn't affect the calculation).
In other words, your input should be 2.1% or 0.021, not 2.1. If you've
input the numbers as 2.1, then multiply them all by 1% (using paste special/
multiply, if you like).

 

[dd]

From the Help file...

If any data point ? 0, GEOMEAN returns the #NUM! error value.

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.

 

[Guest]

David,

I have calculated these values using the formula =((y1/y0)-1)*100. They are
the annual percentage changes or the annual growth rates for the period
1995-2005. I need to calculate the average annual growth rate using geometric
mean. When I use any of the formulae I had mentioned eralier, I am getting
the num error (#NUM!). Have even tried the formulae posted today and now am
getting the value error (#VALUE!).

Can u help me now?

Fenil.

 

[David Biddulph]

So if you have multiplied the numbers by 100, you'll have to divide them
back again (or multiply by 1% as I suggested below). The formulae won't
work if you feed them with 100 times the relevant ratio. [They might
possibly work if you replace the +1 terms by +100, and equivalent terms
elsewhere, but it's safer to work with the relevant numbers directly, rather
than the 100 multiple.]

 

[Guest]

Hi,

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? Would be grateful for all the help I can get.

Fenil.

 

[Ron Rosenfeld]

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

 

[David Biddulph]

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%

Which is, encouragingly, the same answer I get from any of the 4 original
formulae using either the approach I suggested in my message of 08:55
(multiplying the input numbers by 1%), or the alternative I suggested at
11:43 (changing 1s to 100s in the various formulae). [Obviously the first
formula hadn't subtracted 1.]

Both I and another contributor had told the OP that he'd need either to
divide back by 100 (or multiply by 1%) where he'd multiplied by 100, or
change the formulae accordingly, but he seemed to have difficulty
understanding what we'd said. Hopefully your message will have got over the
language barrier.

 

[joeu2004]

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.

 

[Guest]

Hi Ron,

Thank you for your post. My problem got solved and am getting the same
answer. Thank you once again.

Fenil.

 

[Guest]

Hi,

Thank you. It was my mistake. Like you said, I had not commited the formula.
Have got the result now. Thank you once again.

Fenil.

 

[Guest]

Thank you David, for your help and contributions. It helped me a lot. Btw,
its a she, not a he. Thank you once more.

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%

Which is, encouragingly, the same answer I get from any of the 4 original
formulae using either the approach I suggested in my message of 08:55
(multiplying the input numbers by 1%), or the alternative I suggested at
11:43 (changing 1s to 100s in the various formulae). [Obviously the first
formula hadn't subtracted 1.]

Both I and another contributor had told the OP that he'd need either to
divide back by 100 (or multiply by 1%) where he'd multiplied by 100, or
change the formulae accordingly, but he seemed to have difficulty
understanding what we'd said. Hopefully your message will have got over the
language barrier.

 

[David Biddulph]

Glad to hear that the problem is solved (and apologies for the gender
confusion!).
--
David Biddulph

Thank you David, for your help and contributions. It helped me a lot. Btw,
its a she, not a he. Thank you once more.

Fenil.

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

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%

Which is, encouragingly, the same answer I get from any of the 4 original
formulae using either the approach I suggested in my message of 08:55
(multiplying the input numbers by 1%), or the alternative I suggested at
11:43 (changing 1s to 100s in the various formulae). [Obviously the
first
formula hadn't subtracted 1.]

Both I and another contributor had told the OP that he'd need either to
divide back by 100 (or multiply by 1%) where he'd multiplied by 100, or
change the formulae accordingly, but he seemed to have difficulty
understanding what we'd said. Hopefully your message will have got over
the
language barrier.

 

[Ron Rosenfeld]

Hi Ron,

Thank you for your post. My problem got solved and am getting the same
answer. Thank you once again.

Fenil.

You're welcome. Glad you got it working.
--ron