Average without two lowest values

From Chip Pearson?s web site (http://www.cpearson.com/excel/excelF.htm)

?To average the N largest numbers in a range, use the array formula

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

Change "1:10" to "1:N" where N is the number of valuesto average.?

An array is entered using Ctrl+Shift+Enter. It will appear with curley brackets { }, but they can not be entered from the keyboard.

Here?s another take

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:"&COUNT(A1:A60)))))

A1:A60 could be replaced with a range name:

You can create a dynamic range name by defining the name (Insert>Name>Define)like this:

Enter the name (Fred) and put the following in the definition box.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(sheet1!$A:$A),)

The array formula would then be:

=AVERAGE(LARGE(Fred,ROW(INDIRECT("1:"&COUNT(Fred)))))

This will resize itself as the list expands.

You can use the following formula to achieve your average.

=(SUM(A1:A5)-(MIN(A1:A5)+SMALL(A1:A5,2)))/(COUNT(A1:A5)-2)

The reference in the formula using the word small actually would better be termed smallest the ,2 is referencing the second smallest number in the list of values.

You could use Small(A1:A5,1)instead of the Min(A1:A5) the result is the same - the smallest number in the cell range.

Count(A1:A5) returns the number of cells in the range. You must then subtract 2 from this number for the 2 values you are not wanting to include in the average.

I think you get the drift.

Hope this helped,

Mark