شما آن را در المپیک و سایر رویدادهای ورزشی دیده اید: میانگین امتیاز یک ورزشکار با بیرون انداختن بالاترین امتیاز و کمترین امتیاز و سپس میانگین گرفتن بقیه تعیین می شود. ممکن است نیاز به انجام انواع میانگین های مشابه داشته باشید. به عنوان مثال، ممکن است شما یک معلم باشید و لازم باشد قبل از محاسبه میانگین، دو نمره پایین ترین تکلیف را حذف کنید.
برای انجام این نوع میانگین گیری، تنها چیزی که باید به خاطر داشته باشید این است که میانگین با جمع کردن تمام مقادیر یک محدوده و سپس تقسیم آن مجموع بر تعداد آیتم های آن محدوده محاسبه می شود. تابع SUM به راحتی مجموع را ارائه می دهد و از تابع COUNT می توان برای یافتن تعداد موارد در محدوده استفاده کرد. چگونه دو مقدار پایین را در محدوده حذف کنیم؟ می توانید از تابع SMALL استفاده کنید.
فرمول زیر را در نظر بگیرید، که فرض می کند می خواهید میانگین تنظیم شده ای از محدوده A10:A14 را پیدا کنید:
=(SUM(A10:A14)-SMALL(A10:A14,1)-SMALL(A10:A14,2))/(COUNT(A10:A14)-2)
تابع SMALL برای تعیین دو کمترین مقدار در محدوده استفاده می شود و اینها از مجموع کلی محدوده کم می شوند. سپس مقدار حاصل بر تعداد مقادیر موجود در محدوده تقسیم می شود. همچنین توجه داشته باشید که مقدار COUNT برای جبران این واقعیت که شما دو مقدار پایین را نادیده می گیرید، 2 کاهش می یابد.
روش دیگر برای محاسبه همان میانگین استفاده از فرمول آرایه است. مورد زیر این ترفند را انجام می دهد:
=AVERAGE(IF(A10:A14>SMALL(A10:A14,2),A10:A14))
از آنجایی که این یک فرمول آرایه است، باید آن را با فشار دادن Ctrl+Shift+Enter به جای فشار دادن Enter وارد کنید . این فرمول همچنان به استفاده از تابع SMALL متکی است، اما از تابع AVERAGE واقعی نیز برای برگرداندن نتیجه استفاده می کند. از آنجایی که این یک فرمول آرایه است، هر یک از مقادیر آرایه (محدوده) را بررسی می کند و تنها در صورتی که از دومین مقدار کوچکتر در آرایه بزرگتر باشند، آنها را برای استفاده در میانگین در نظر می گیرد.
در حالی که فرمول آرایه کوتاه تر از فرمول معمولی طولانی تر است، یک اخطار وجود دارد که باید در نظر داشت: اگر یک "Tea" دو طرفه در دومین مقدار در محدوده وجود داشته باشد، فرمول آرایه نتیجه نامطلوبی خواهد داشت، یا یک کراوات سه طرفه در کمترین مقدار. به عنوان مثال، اگر مقادیر میانگین 3، 2، 10، 3 و 7 باشد، فرمول آرایه به طور متوسط 8.5 تولید می کند. چرا؟ زیرا فقط مقادیر 10 و 7 بالاتر از مقدار دوم کمتر هستند و میانگین آن دو 8.5 است. اگر از فرمول طولانی تری استفاده می کنید که ابتدا در بالا ارائه شد، میانگین بازگشتی 6.666667 است که میانگین 10، 3 و 7 است.
