肯(Ken)撰写了有关GEOMEAN函数的问题。当他尝试在大量值(3,500行数据)上使用该函数时,他将返回一个#NUM错误值。

GEOMEAN函数用于返回一系列值的几何平均值。 n个数字的GEOMEAN是数字乘积的第n个根。例如,如果一个系列(A到D)中有四个值,则这些数字的乘积为A B C * D,而GEOMEAN是该乘积的第四根。

如果满足以下三个条件中的任何一个,则将返回#NUM错误:任何值等于零,任何值都为负,或者超出了Excel的限制。肯可能会遇到这最后一个条件,特别是如果他的3500个值中的任何一个很大时。

由于GEOMEAN会找到3,500个数字的乘积(将它们彼此相乘),然后取第n个根,因此乘积对于Excel可能很容易过大。 Excel中的最大正数是9.99999999999999 * 10 ^ 307(以科学计数法表示为9.99999999999999E + 307)。如果乘积大于该数字,则该函数将显示#NUM错误。

解决方案是使用日志进行计算。当您查看GEOMEAN函数的转换时,这最容易理解:

GEOMEAN = (X1X2X3...Xn)^ (1/n)

ln(GEOMEAN) = ln((X1X2X3...Xn)^ (1/n))

ln(GEOMEAN) = (1/n)  ln(X1X2X3...*Xn)

ln(GEOMEAN) = (1/n) * (ln(X1)+ln(X2)+ln(X3)+...+ln(Xn))

ln(GEOMEAN) = average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn))

GEOMEAN = exp(average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)))

如果按照上述步骤进行操作,则会发现GEOMEAN等于值的对数平均值的指数。您可以使用以下数组公式而不是GEOMEAN函数来计算所需的结果:

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

假定所需值在A1:A3500范围内。由于它是一个数组公式,因此必须使用Ctrl + Shift + Enter将其输入到单元格中。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(2580)适用于Microsoft Excel 97、2000、2002和2003。可以在以下功能区中为Excel的功能区界面(Excel 2007及更高版本)找到本技巧的版本: