max of average

[dmatrix00d]

how would you take the max of an average in a situation such as this:

1 2 3
3 4 2
2 3 4

I want to average the rows, and then find the max of each of those
averages.

naturally this should be 3.

i think you have to use one of those control shift enter formulas, but
i'm not sure.

 

[T. Valko]

Try this:

Array entered ("one of those control shift enter formulas")

=MAX(SUBTOTAL(1,OFFSET(A1:C3,ROW(A1:C3)-ROW(A1),,1)))

Biff

 

[dmatrix00d]

wow that worked.
can you explain to me why for future purposes?

thank you very much

 

[Guest]

=MAX(AVERAGE(A1:A3),AVERAGE(B1:B3),AVERAGE(C1:C3))

 

[T. Valko]

The Subtotal function gets the average of each row and passes those averages
to the Max function.

The Offset tells the Subtotal which cells to average.

ROW(A1:C3)-ROW(A1) tells the Offset where to find the cells to average:
offset the range by 0,1,2 rows.

So:

Average(A1:C3, Offset 0 rows and 0 columns 1 row high)
Average(A1:C3, Offset 1 rows and 0 columns 1 row high)
Average(A1:C3, Offset 2 rows and 0 columns 1 row high)

This is what it would look like:

=MAX(AVERAGE(A1:C1),AVERAGE(A2:C2),AVERAGE(A3:C3))

If you only had 3 rows of data you'd be better off using the above formula.
(less complicated, not an array, not volatile)

Biff

 

[Guest]

dmatrix'

using Valko's concept

try this for a productive array Rows x Columns = range <defined name>

=MAX(SUBTOTAL(1,OFFSET(range,ROW(range)-MIN(ROW(range)),,1)))

note: you can insert or reduce <rows or columns> but there should be no
completely blank row.

dont forget the ctrl-shft-enter on the formula when you resize the range.