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.

Try this:

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

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

Biff

wrote in message
oups.com...
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.

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

thank you very much


T. Valko wrote:
Try this:

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

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

Biff

wrote in message
oups.com...
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.

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

wrote in message
ups.com...
wow that worked.
can you explain to me why for future purposes?

thank you very much


T. Valko wrote:
Try this:

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

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

Biff

wrote in message
oups.com...
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.

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.

" wrote:

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

thank you very much


T. Valko wrote:
Try this:

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

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

Biff

wrote in message
oups.com...
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.

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


" wrote:

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.