Divide all the numbers in your list by a constant (i.e. 1e10). Compute the
geometric mean. Multiply the result by that same constant.
This could suffer from underflow - intermediate products between 0 and 1E-308.
The problem is that the multiplication of all of the 250+ values is
exceeding the biggest number that Excel can handle. You can replicate this
problem by trying to compute the geometric mean of two numbers: 1e300 and
1e100.
Diagnosis correct. However, the most robust work-around involves using
logarithms, without error/bounds checking
=EXP(SUMPRODUCT(LN(Range)/COUNT(Range)))
There are other ways to do this, such as
=PRODUCT(Range^(1/COUNT(Range)))
which is better if you need to reduce nested function calls.
The two formulas above do lose some precision. An alternative approach involves
partitioning your range, e.g.,
=GEOMEAN(GEOMEAN(OFFSET(Range,0,0,INT(COUNT(Range)/4),1)),
GEOMEAN(OFFSET(Range,INT(COUNT(Range)/4),0,INT(COUNT(Range)/4),1)),
GEOMEAN(OFFSET(Range,2*INT(COUNT(Range)/4),0,INT(COUNT(Range)/4),1)),
GEOMEAN(OFFSET(Range,3*INT(COUNT(Range)/4),0,
COUNT(Range)-3*INT(COUNT(Range)/4),1)))
Much longer/nastier, but often more precise.
This is an unfortunate case in which Excel's implementation of a function is too
simple to be broadly useful. Even if the performance drag from scanning the
argument lists twice were prohibitive, there's a one-pass algorithm which
Microsoft could have used that would have prevented overflow/underflow.
gm = 1.0
n = 0
For Each v In ArgumentList
n = n + 1
w = 1.0 / n
gm = v ^ w * gm ^ (1.0 - w)
Next v
Yes, this is slower than just taking the Nth root of the product of N numbers,
but unless that product used extended (or arbitrary) precision, it'll suffer far
too often from overflow/underflow. There's a trade-off between speed and
capacity here. Unfortunately, more often than not Microsoft has chosen speed.
