Identify the row in which a MAX number in a column resides

B

BarDoomed

I am building a worksheet that collects data from a network on a daily basis.
One of the data categories has 9 columns and 8 rows. I am finding the MAX of
each column but need to identify the row in which a MAX number in a column
resides at the same time. Probably pretty simple but for some reason I can't
put it together.

Thanks
DobieG

Why my display named shows up as BarDoomed I don't know?????????
 
M

Mike H

To find the address of the max try this

=ADDRESS(MAX(IF(MAX(A1:E8)=A1:E8,ROW(A1:E8))),MAX(IF(MAX(A1:E8)=A1:E8,COLUMN(A1:E8))),4)

Array entered. This is for A1 - E8

Mike
 
G

Gary''s Student

Say in column G we have:

1
4
2
5
7
6
8
3

In A1 enter:
=MAX(G:G) gives the max value (in this case 8)

In A2 enter:
=MATCH(MAX(G:G),G:G,0) gives the row in which the max can be found (in this
case 7)
 
B

BarDoomed

Thanks to all for the responces. I'll get busy and try them. Let you know the
results.
Thanks again
DobieG
 
B

BarDoomed

The suggestion did exactly what I ask. I need to modify my question slightly.
I have label the rows in column A 1-10. Find the cell in A that matches
the MAX in Col H.

Great so far..
DobieG
 
H

Harlan Grove

Mike H said:
To find the address of the max try this

=ADDRESS(MAX(IF(MAX(A1:E8)=A1:E8,ROW(A1:E8))),MAX(IF(MAX(A1:E8)=A1:E8,COLUMN(A1:E8))),4)
Click to expand...
....

You've expanded this to 2D, but you failed to take into account the
complications involved. For example, if A1:E8 contained

1 1 1 1
1 1 9 1
1 1 1 1
1 1 1 1
1 1 1 1
9 1 0 1
1 1 1 1
1 1 1 1

the address of the max would be either C2 (search by row then column)
or A6 (search by column then row), but your formula would return C6,
which FTHOI I've made the min value.

Back to the drawing board!
 
M

Mike H

Drat, Permission to say ****

Harlan Grove said:
....

You've expanded this to 2D, but you failed to take into account the
complications involved. For example, if A1:E8 contained

1 1 1 1
1 1 9 1
1 1 1 1
1 1 1 1
1 1 1 1
9 1 0 1
1 1 1 1
1 1 1 1

the address of the max would be either C2 (search by row then column)
or A6 (search by column then row), but your formula would return C6,
which FTHOI I've made the min value.

Back to the drawing board!
Click to expand...
 
Top