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

  • Thread starter Thread starter BarDoomed
  • Start date Start date
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?????????
 
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
 
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)
 
Thanks to all for the responces. I'll get busy and try them. Let you know the
results.
Thanks again
DobieG
 
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
 
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!
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare identifier columns from 2 sheets - remove rows that are dups 2
MAX only Rows in Range where another Row is Not Blank 3
Conditional formatting based on max in range of other columns 2
Finding identical rows in a list 6
Find the maximum value in a row or column with text and numbers 2
Max function for row with named cells 3
How do I reference the cell which returned a lookup value? 1
Find max Row() number for a value in column 9

Back
Top