کن در مورد مشکلی که با تابع GEOMEAN داشت نوشت. هنگامی که او سعی می کند از تابع در تعداد زیادی از مقادیر (3500 ردیف داده) استفاده کند، مقدار خطای #NUM برگردانده می شود.
تابع GEOMEAN برای برگرداندن میانگین هندسی یک سری مقادیر استفاده می شود. GEOMEAN n عدد ریشه n-امین حاصلضرب اعداد است. به عنوان مثال، اگر چهار مقدار در یک سری وجود داشته باشد (A تا D)، حاصلضرب آن اعداد A * B * C * D است و GEOMEAN ریشه چهارم آن محصول است.
اگر هر یک از سه شرط وجود داشت: هر یک از مقادیر برابر با صفر بود، هر یک از مقادیر منفی بود، یا از محدودیت های اکسل فراتر رفت، خطای #NUM برگردانده می شد. احتمالاً این آخرین وضعیتی است که کن درگیر آن است، به خصوص اگر هر یک از مقادیر 3500 او بزرگ باشد.
از آنجایی که GEOMEAN حاصل ضرب 3500 عدد را پیدا می کند (همه آنها را در یکدیگر ضرب می کند) و سپس ریشه n را می گیرد، ممکن است محصول به راحتی برای اکسل خیلی بزرگ باشد. بزرگترین عدد مثبت در اکسل 9.99999999999999 * 10^307 است (در نماد علمی این عدد به صورت 9.99999999999999E+307 نوشته شده است). اگر محصول از این عدد بزرگتر شود، یک خطای #NUM برای تابع دریافت خواهید کرد.
راه حل این است که از لاگ برای انجام محاسبات استفاده کنید. وقتی به تبدیل تابع GEOMEAN نگاه می‌کنید، درک این ساده‌تر است:
GEOMEAN = (X1*X2*X3*...*Xn)^ (1/n)
ln(GEOMEAN) = ln((X1*X2*X3*...*Xn)^ (1/n))
ln(GEOMEAN) = (1/n) * ln(X1*X2*X3*...*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 وارد یک سلول کنید .
