I hope someone can help me with this! I am trying to create a formula that will select the last 10 numbers in a row then drop the highest and the lowest number and return an average for the remaining 8 numbers. Row 1 on the worksheet is a list of dates and row 2 is a number for that date. Not all cells in row 2 have data in them some are blank and should not count in when retrieving the last 10.

Any help with this would be great!

When you say "last 10", do you mean the last 10 dates with a number in row 2?

That is correct.

On Fri, 29 Mar 2013 22:19:18 +0000, dpsgolfer wrote:


I hope someone can help me with this! I am trying to create a formula
that will select the last 10 numbers in a row then drop the highest and
the lowest number and return an average for the remaining 8 numbers. Row
1 on the worksheet is a list of dates and row 2 is a number for that
date. Not all cells in row 2 have data in them some are blank and should
not count in when retrieving the last 10.

Any help with this would be great!

This formula must be **array-entered**:

=AVERAGE(LARGE(INDIRECT(ADDRESS(2,LARGE(ISNUMBER($ 2:$2)*
COLUMN($2:$2),{1,2,3,4,5,6,7,8,9,10}))),{2,3,4,5,6 ,7,8,9}))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.