MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula needed


Posted by AK on December 05, 2001 9:14 PM

I would like to calculate an average of 4 number from a list of
5 numbers, not taking into the concidration the lowest number.

For example number 2,5,7,10 and 4
i want to excell to find the lowest number and then give me the average for the other four numbers.
\Can anyone help me please.


Posted by Juan Pablo G. on December 05, 2001 9:42 PM

How about

=(SUM(A1:A5)-MIN(A1:A5))/4

If there are two or more min's, then this formula only removes one of them.

Juan Pablo G.

Posted by Colo on December 06, 2001 12:13 AM

Hi.
This is another solution though this question has already been
solved by Mr.Juan Pablo G.

=SUMIF(A1:A5,"<>"&MIN(A1:A5))/(COUNT(A1:A5)-COUNTIF(A1:A5,MIN(A1:A5)))

If there are more than two MIN's, then this formula removes all of them.