ExcelBanter - How do I drop the lowest 2 numbers and then average?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - How do I drop the lowest 2 numbers and then average? (https://www.excelbanter.com/excel-worksheet-functions/7515-how-do-i-drop-lowest-2-numbers-then-average.html)

nightlynik

How do I drop the lowest 2 numbers and then average?

Please explain this so that I can understand!

Frank Kabel

Hi
one way:
=(SUM(A1:A100)-SUM(SMALL(A1:A100,{1,2})))/(COUNTA(A1:A100)-2)

--
Regards
Frank Kabel
Frankfurt, Germany

"nightlynik" schrieb im
Newsbeitrag ...
Please explain this so that I can understand!

N Harkawat

=AVERAGE(SMALL(A:A,ROW(INDIRECT("1:"&COUNTA(A:A)-2))))
Array entered (ctrl+shift+enter)

"nightlynik" wrote in message
...
Please explain this so that I can understand!

N Harkawat

Sorry you needed to exclude the smallest 2 entries
Change "small" to "large" so correct formula will be
=AVERAGE(LARGE(A:A,ROW(INDIRECT("1:"&COUNTA(A:A)-2))))
Array entered (ctrl+shift+enter)

"N Harkawat" wrote in message
...
=AVERAGE(SMALL(A:A,ROW(INDIRECT("1:"&COUNTA(A:A)-2))))
Array entered (ctrl+shift+enter)

"nightlynik" wrote in message
...
Please explain this so that I can understand!

All times are GMT +1. The time now is 08:39 AM.