Ken đã viết về một vấn đề mà anh ấy đang gặp phải với hàm GEOMEAN. Khi anh ta cố gắng sử dụng hàm trên một số lượng lớn giá trị (3.500 hàng dữ liệu), anh ta nhận được giá trị lỗi #NUM được trả về.

Hàm GEOMEAN được sử dụng để trả về giá trị trung bình hình học của một chuỗi giá trị. GEOMEAN của n số là căn bậc n của tích các số. Ví dụ: nếu có bốn giá trị trong một chuỗi (A đến D), thì tích của những số đó là A B C * D và GEOMEAN là căn thứ tư của tích đó.

Lỗi #NUM sẽ được trả về nếu đáp ứng bất kỳ điều kiện nào trong ba điều kiện: bất kỳ giá trị nào bằng 0, bất kỳ giá trị nào là âm hoặc vượt quá giới hạn của Excel. Có khả năng đây là điều kiện cuối cùng mà Ken gặp phải, đặc biệt nếu bất kỳ giá trị nào trong số 3.500 của anh ta lớn.

Vì GEOMEAN tìm tích của 3.500 số (nhân tất cả chúng với nhau) và sau đó lấy số gốc thứ n, nên tích có thể dễ dàng quá lớn đối với Excel. Số dương lớn nhất trong Excel là 9,99999999999999 * 10 ^ 307 (trong ký hiệu khoa học, số này được viết là 9,99999999999999E + 307). Nếu sản phẩm lớn hơn con số này, bạn sẽ gặp lỗi #NUM cho hàm.

Giải pháp là sử dụng các bản ghi để thực hiện phép tính. Điều này dễ hiểu nhất khi bạn nhìn vào một biến đổi của hàm 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)))

Nếu bạn theo dõi phần trên, bạn thấy rằng GEOMEAN tương đương với số mũ của giá trị trung bình của các bản ghi của các giá trị. Bạn có thể tính toán kết quả mong muốn bằng cách sử dụng công thức mảng sau thay vì hàm GEOMEAN:

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

Điều này giả định rằng các giá trị mong muốn nằm trong phạm vi A1: A3500. Vì nó là một công thức mảng, bạn phải nhập nó vào một ô bằng cách sử dụng Ctrl + Shift + Enter.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2580) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: