Excel Geomean 返回#value!有时？

Question

我修改了vba下面的功能以满足我的需要。

我有许多工作簿，其中包含 4500 多行的工作表，我使用该函数搜索两个给定值(作为边界)。然后，它选择行作为范围。最后，在该范围内做任何事情。功能:

Function GeoM(A, B)

Application.Volatile    
Dim x As Integer
Dim y As Integer
Dim rng As Range

x = Application.WorksheetFunction.Match(A, Range("B:B"), 0) ' looking in col B
y = Application.WorksheetFunction.Match(B, Range("B:B"), 0) ' looking in col B

Set rng = Range(Cells(x, 18), Cells(y, 18)) 'Im working on col 18
GeoM = Application.WorksheetFunction.GeoMean(rng)
End Function

问题是，这段代码工作得很好，除了 GeoMean n.我注意到当数据范围相对较小(数据单元格的数量)时，它会返回一个值。但是，如果范围大于约。 126 个单元格，返回 #value! .

我被困住了，正在努力解决这个问题。是 GeoMean功能仅限于给定数量的数据？

谢谢

Answer 1

我对早期 Excel 版本的测试(我在 xl03 中测试)似乎有 170 个字符的限制，在此 Mr Excel thread 中进行了验证

(XL10 在较长的数据集上运行良好)

我也试过:

使用 Evaluate

使用一维数组

sample 不合格

Dim X
Set rng1 = Range("A1:A171")
MsgBox Evaluate("GeoMean(A1:A171)")
X = Application.Transpose(rng1)
MsgBox Application.WorksheetFunction.GeoMean(X)

无济于事。

所以我认为你的两种解决方法是:

通过 VBA 将公式插入 Excel 并使用此结果

根据 MrExcel 线程使用 GeoMean 的推导，即=EXP(AVERAGE(LN(Range)))

建议的方法

MsgBox Evaluate("EXP(AVERAGE(LN(A1:A171)))")