Using the Max Function to determine the highest average

Question

If there are no number values in some of these averages, it returns #DIV/. What is the best way to exclude the averages that don't contain #'s?

A second question is what formula would I need to use to determine the next largest and so on?

 Thank you

=MAX(AVERAGE($B$10:$B$21),AVERAGE($C$10:$C$21),AVERAGE($D$10:$D$21),AVERAGE($E$10:$E$21),AVERAGE($F$10:$F$21),AVERAGE($G$10:$G$21),AVERAGE($H$10:$H$21),AVERAGE($I$10:$I$21),AVERAGE($J$10:$J$21),AVERAGE($K$10:$K$21))

Answer 1

If there are no number values in some of these averages, it returns #DIV/. What is the best way to exclude the averages that don't contain #'s?

A second question is what formula would I need to use to determine the next largest and so on?

 Thank you

=MAX(AVERAGE($B$10:$B$21),AVERAGE($C$10:$C$21),AVERAGE($D$10:$D$21),AVERAGE($E$10:$E$21),AVERAGE($F$10:$F$21),AVERAGE($G$10:$G$21),AVERAGE($H$10:$H$21),AVERAGE($I$10:$I$21),AVERAGE($J$10:$J$21),AVERAGE($K$10:$K$21))

Why not use a summay row?

On row 22 enter a formula like this in B22:

=IF(COUNT(B10:B21),AVERAGE(B10:B21),"")

Copy across to K22

Then your MAX formula is simple:

=MAX(B22:K22)

And for the nth largest value:

=LARGE(B22:K22,n)

Where n = the nth value you want.

--

Biff

Microsoft Excel MVP

Answer 2

Hi,

I had tried this on a smaller data set and that is why the A5:J5 - I forgot to change that

--

Regards,

Ashish Mathur

Microsoft Excel MVP

www.ashishmathur.com

"Ashish Mathur" wrote in message news:4ab99946-0a5c-49aa-ac58-b1baafd29750...

Hi,

Try this array formula (Ctrl+Shift+Enter) for ignoring errors.

=MAX(IF((ISERROR(SUBTOTAL(1,OFFSET(B10,12,COLUMN(A5:J5)-1,-13,1)))),"",SUBTOTAL(1,OFFSET(B10,12,COLUMN(A5:J5)-1,-13,1))))

--

Regards,

Ashish Mathur

Microsoft Excel MVP

www.ashishmathur.com

"dak" wrote in message news:1dc655d0-30bd-40e1-87ea-d6171256265b...

If there are no number values in some of these averages, it returns #DIV/. What is the best way to exclude the averages that don't contain #'s?

A second question is what formula would I need to use to determine the next largest and so on?

 Thank you

=MAX(AVERAGE($B$10:$B$21),AVERAGE($C$10:$C$21),AVERAGE($D$10:$D$21),AVERAGE($E$10:$E$21),AVERAGE($F$10:$F$21),AVERAGE($G$10:$G$21),AVERAGE($H$10:$H$21),AVERAGE($I$10:$I$21),AVERAGE($J$10:$J$21),AVERAGE($K$10:$K$21))

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Answer 3

Hi,

Try this array formula (Ctrl+Shift+Enter) for ignoring errors.

=MAX(IF((ISERROR(SUBTOTAL(1,OFFSET(B10,12,COLUMN(A5:J5)-1,-13,1)))),"",SUBTOTAL(1,OFFSET(B10,12,COLUMN(A5:J5)-1,-13,1))))

Why COLUMN(A5:J5)? How does A5:J5 relate to the range where the data is actually located, B10:K21?

COLUMN(A5:J5) is not very intuitive (not at all!)

Try it like this:

=MAX(IFERROR(SUBTOTAL(1,OFFSET(B10:B21,,COLUMN(B10:K21)-COLUMN(B10))),""))

Or:

=LARGE(IFERROR(SUBTOTAL(1,OFFSET(B10:B21,,COLUMN(B10:K21)-COLUMN(B10))),""),N)

Where N = the nth largest average that you want.

--

Biff

Microsoft Excel MVP

Answer 4

Hi,

Try this array formula (Ctrl+Shift+Enter) for ignoring errors.

=MAX(IF((ISERROR(SUBTOTAL(1,OFFSET(B10,12,COLUMN(A5:J5)-1,-13,1)))),"",SUBTOTAL(1,OFFSET(B10,12,COLUMN(A5:J5)-1,-13,1))))

--

Regards,

Ashish Mathur

Microsoft Excel MVP

www.ashishmathur.com

"dak" wrote in message news:1dc655d0-30bd-40e1-87ea-d6171256265b...

If there are no number values in some of these averages, it returns #DIV/. What is the best way to exclude the averages that don't contain #'s?

A second question is what formula would I need to use to determine the next largest and so on?

 Thank you

=MAX(AVERAGE($B$10:$B$21),AVERAGE($C$10:$C$21),AVERAGE($D$10:$D$21),AVERAGE($E$10:$E$21),AVERAGE($F$10:$F$21),AVERAGE($G$10:$G$21),AVERAGE($H$10:$H$21),AVERAGE($I$10:$I$21),AVERAGE($J$10:$J$21),AVERAGE($K$10:$K$21))

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com