Кен написал о проблеме, возникшей у него с функцией GEOMEAN. Когда он пытается использовать функцию для большого количества значений (3500 строк данных), он получает возвращаемое значение ошибки #NUM.

Функция GEOMEAN используется для возврата среднего геометрического ряда значений. GEOMEAN n чисел — это корень n-й степени от произведения чисел. Например, если в ряду четыре значения (от A до D), то произведение этих чисел будет A B C * D, а GEOMEAN — это корень четвертой степени этого произведения.

Ошибка #NUM будет возвращена, если будет выполнено любое из трех условий: любое из значений было равно нулю, любое из значений было отрицательным или были превышены пределы Excel. Вероятно, именно это последнее условие, с которым столкнулся Кен, особенно если какое-либо из его 3500 значений велико.

Поскольку GEOMEAN находит произведение 3500 чисел (умножает их все друг на друга), а затем берет корень n-й степени, произведение может легко оказаться слишком большим для Excel. Наибольшее положительное число в Excel — 9,99999999999999 * 10 ^ 307 (в экспоненциальном представлении это записывается как 9,99999999999999E + 307). Если продукт станет больше этого числа, вы получите ошибку # ЧИСЛО для функции.

Решение — использовать журналы для расчета. Это легче всего понять, если посмотреть на преобразование функции 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 и новее) здесь:

link: / excelribbon-Using_GEOMEAN_with_a_Large_List [Использование GEOMEAN с большим списком].