Problem with Geomean Function

[Nadeem Shafique Butt]

I am trying to findout the Geometric Mean of 1517 values but i got an error
message when I use geomean function, appearently ther is no value <=0. Can
anyone help me in sorting out this problem

 

[Bernie Deitrick]

Nadeem,

Based on very little testing, I can only find the GeoMean of a limited number (around 100) of values
(actual limit depends on the values) with one formula. But....

GEOMEAN(A1:A200) = GEOMEAN(B1:B2)
where
B1 =GEOMEAN(A1:A100)
B2 =GEOMEAN(A101:A200)

So if you make up a formula like

=GEOMEAN(OFFSET($A$1,(ROW()-1)*50,0,50))

and copy down until you get an error, then delete the last one - you need to do equal groups - you
should be able to get the geomean of those formulas, then you should be able to do 1500 values
(multiples of 50) very easily.

HTH,
Bernie
MS Excel MVP