Calculating Geometric Mean w a large data set

[z168]

I have a data set of 11,000 numbers with no more than 2 digits per number. I have to get the geometric mean but excel 2010 limits to 5.0748E+307 (or roughly less than a 1000 points).

is there a workaround that I can tackle this?

thank you

 

[z168]

I figured it out

 

[z168]

I got a PM to resolve this question. so here it goes

Step1: convert all numbers to Logarithm; formula is =ln(x)
Step2: add all converted numbers
Step3: divide the sum total of the logarithms by the total count
Step4: do the inverse log, and there is your geometric mean; formula is =exp(x)

 

[ZVI]

Below is UDF for calculating of Geometric Mean of a large data set.
Usage in formula: =GeometricMean(A1:B10000)

Function GeometricMean(Rng)
' ZVI:2014-06-03 http://www.mrexcel.com/forum/excel-questions/693777-calculating-geometric-mean-w-large-data-set.html
  Dim a, x
  Dim n As Double, v As Double
  Dim vt As VbVarType
  If IsArray(Rng) Then a = Rng Else a = Array(Rng)
  For Each x In a
    vt = VarType(x)
    If vt > 1 And vt < 7 Then
      If x > 0 Then
        n = n + 1
        v = v + Log(x)
      End If
    End If
  Next
  If n Then GeometricMean = Exp(v / n) Else Err.Raise 13
End Function

 

[shg]

Or via formula, confirmed with Ctrl+Shift+Enter

=EXP(AVERAGE(LN(A1:A11000)))

 

[ZVI]

Or via formula, confirmed with Ctrl+Shift+Enter

=EXP(AVERAGE(LN(A1:A11000)))

Hi Shg,

For regret LN(A1:A11000) has the same overflow limitation.
For example, LN(A1:A1000) returns #NUM! if cells A1:A1000 are populated by 3

Vlad

 

[ZVI]

Corrected: LN(A1:A1001) fails for A1:A1001 = 3

 

[shg]

Not seeing that result, ZVI, e.g., for A1:A11000 = 9E307.

 

[ZVI]

Shg, I'm apologizing.
You formula works well, it was empty cell in my testing range. UDF skips such cells.
Vlad

 

[shg]

A fair point; it should ignore empty cells.

=EXP(AVERAGE(IF(ISNUMBER(A1:A11000), LN(A1:A11000))))