Min, Sum, Average

  • Thread starter Thread starter Jesse
  • Start date Start date
J

Jesse

I need to drop the lowest(1)score of (4) scores and average the rest
for 400 records.
What is my formula...to drop that lowest number and average the other
3? I don't want to have to enter 400 averages by hand. There must be a
way.
Thank you.
 
Jesse, how about something like this
=(SUM(A1:A14)-SMALL(A1:A14,1))/(COUNT(A1:A14)-1)

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Hi Jesse,

Here are a couple of examples.

=AVERAGE(LARGE(A1:A4,ROW(INDIRECT("1:3"))))

Array entered - CTRL+SHIFT+ENTER
If the min value is duplicated, this formula will include
a single instance of that value.

OR

=AVERAGE(IF(A1:A4>MIN(A1:A4),A1:A4))

Array entered - CTRL+SHIFT+ENTER
This formula will exclude all duplicate min values.

Adjust ranges as needed.

Biff
 
Back
Top