Ken은 GEOMEAN 함수와 관련된 문제에 대해 썼습니다. 많은 값 (3,500 행의 데이터)에 대해 함수를 사용하려고하면 #NUM 오류 값이 반환됩니다.

GEOMEAN 함수는 일련의 값의 기하 평균을 반환하는 데 사용됩니다. n 개의 숫자의 GEOMEAN은 숫자 곱의 n 번째 근입니다. 예를 들어 계열에 4 개의 값 (A-D)이있는 경우 해당 숫자의 곱은 A B C * D이고 GEOMEAN은 해당 곱의 네 번째 루트입니다.

다음 세 가지 조건 중 하나라도 충족되면 #NUM 오류가 반환됩니다.

값이 0이거나 음수이거나 Excel 한계를 초과했습니다. 특히 3,500 개의 값 중 하나가 큰 경우 Ken이 실행하는 마지막 조건 일 가능성이 높습니다.

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 교육을위한 소스입니다.

이 팁 (9328)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다. 여기에서 Excel의 이전 메뉴 인터페이스에 대한이 팁 버전을 찾을 수 있습니다.

link : / excel-Using_GEOMEAN_with_a_Large_List [대규모 목록과 함께 GEOMEAN 사용].