=MAX-Return Cell info

[Guest]

Please help all you clever people !!

I have a table in the format:
MR A MR B MR C MR D
01/01/06 1 3 7 8
02/01/06 2 5 4 4
03/01/06 3 9 5 7

I want a formula to analyse the entire table (Which can change) and to
return me who has achieved the Max value and on which date.

The answer here should be MR B on the 03/01/06.

PLEASE HELPPP!!!

 

[Dave Peterson]

To get Mr. B:
=INDEX(B1:E1,MIN(IF(B2:E4=MAX(B2:E4),COLUMN(B2:E4)-1)))
to get the 3/1/2006
=INDEX(A2:A4,MIN(IF(B2:E4=MAX(B2:E4),ROW(B2:E4)-1)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly enough, you
can use the whole row.

 

[Biff]

What if there are duplicate max values?

Biff

 

[Dave Peterson]

Ooh. Excellent point.

I'll await one of your formulas!

 

[Biff]

I'll await a reply from the OP!

Biff

Ooh. Excellent point.

I'll await one of your formulas!

 

[Guest]

Yes, you are right...it could be repeated...unlikely...but possible ! Dammit!!
I cant quite get the date formulae to work....could it be because my data is
in the range B241..F267? I can get the name bit right no problem -

{=INDEX($B$241:$F$241,MIN(IF(C242:F267=MAX(C242:F267),COLUMN(C242:F267)-1)))}

I'll await a reply from the OP!

Biff

 

[Guest]

Could still do with some help on this guys !! Please!

Yes, you are right...it could be repeated...unlikely...but possible ! Dammit!!
I cant quite get the date formulae to work....could it be because my data is
in the range B241..F267? I can get the name bit right no problem -

{=INDEX($B$241:$F$241,MIN(IF(C242:F267=MAX(C242:F267),COLUMN(C242:F267)-1)))}

 

[Dave Peterson]

I think that I would never come up with a formula that could handle ties. But
maybe a little user defined function would work for you???

Option Explicit
Function myLabel(rng As Range) As String

Dim myMax As Double
Dim TableRng As Range
Dim NumberOfMatches As Long

Dim mCtr As Long
Dim myStr As String

Dim iCol As Long
Dim iRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim FirstRow As Long
Dim LastRow As Long

With rng
Set TableRng = .Resize(.Rows.Count - 1, _
.Columns.Count - 1).Offset(1, 1)
End With

myMax = Application.Max(TableRng)
NumberOfMatches = Application.CountIf(TableRng, myMax)

mCtr = 0
myStr = ""
With TableRng
FirstCol = .Column
LastCol = .Cells(.Cells.Count).Column
FirstRow = .Row
LastRow = .Cells(.Cells.Count).Row
End With

With rng.Parent
For iCol = FirstCol To LastCol
For iRow = FirstRow To LastRow
If .Cells(iRow, iCol).Value = myMax Then
myStr = myStr & "; " & .Cells(iRow, FirstCol - 1).Text _
& "--" & .Cells(FirstRow - 1, iCol).Text
mCtr = mCtr + 1
If mCtr = NumberOfMatches Then
Exit For
End If
End If
Next iRow
Next iCol
End With

If myStr = "" Then
'do nothing
Else
myStr = Mid(myStr, 3)
End If

myLabel = myStr

End Function

This goes in a general module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Then you can use it like any other function:

=mylabel(G16:K19)

And returns something like:
01/01/2006--MR A; 03/01/2006--MR B; 02/01/2006--MR C

 

[Biff]

Well, ties opens up a big can of worms!

Consider this: the ties might be by more than one person on the same day.
See this screencap:

http://img325.imageshack.us/img325/4634/max9sy.jpg

The bordered box is where the data has been extracted to.

To extract the dates you'll need a helper column (I hate helper columns!).
In this example I used column F and then hid that column.

Enter this formula in F2 and copy down to F10:

=IF(COUNTIF(B2:E2,MAX(B$2:E$10)),ROW(),"")

Enter this formula in A13 to extract the date(s):

=IF(ROWS($1:1)<=COUNT(F$2:F$10),INDEX(A$2:A$10,MATCH(SMALL(F$2:F$10,ROWS($1:1)),F$2:F$10,0)),"")

Enter this formula in A14 as an array using the key combo of
CTRL,SHIFT,ENTER and copy over to E13:

=IF($A13="","",INDEX($B$1:$E$1,SMALL(IF(OFFSET(INDEX($A$2:$A$10,MATCH($A13,$A$2:$A$10,0)),,4,,-4)=MAX($B$2:$E$10),COLUMN($B1:$E1)-COLUMN($B1)+1),COLUMNS($A:A))))

Now, select A13:E13 then copy down to enough cells to account for all the
possible ties.

In the above formula, in the Offset function, the 4 and -4 are the number of
data columns in the table.

No error trapping/checking in the above formula. I used conditional
formatting to hide them.

Biff

 

[Biff]

Typo correction:

Enter this formula in A14 as an array using the key combo of
CTRL,SHIFT,ENTER and copy over to E13:

Should read:

Enter this formula in B13 as an array using the key combo of
CTRL,SHIFT,ENTER and copy over to E13:

Biff

Well, ties opens up a big can of worms!

Consider this: the ties might be by more than one person on the same day.
See this screencap:

http://img325.imageshack.us/img325/4634/max9sy.jpg

The bordered box is where the data has been extracted to.

To extract the dates you'll need a helper column (I hate helper columns!).
In this example I used column F and then hid that column.

Enter this formula in F2 and copy down to F10:

=IF(COUNTIF(B2:E2,MAX(B$2:E$10)),ROW(),"")

Enter this formula in A13 to extract the date(s):

=IF(ROWS($1:1)<=COUNT(F$2:F$10),INDEX(A$2:A$10,MATCH(SMALL(F$2:F$10,ROWS($1:1)),F$2:F$10,0)),"")

Enter this formula in A14 as an array using the key combo of
CTRL,SHIFT,ENTER and copy over to E13:

=IF($A13="","",INDEX($B$1:$E$1,SMALL(IF(OFFSET(INDEX($A$2:$A$10,MATCH($A13,$A$2:$A$10,0)),,4,,-4)=MAX($B$2:$E$10),COLUMN($B1:$E1)-COLUMN($B1)+1),COLUMNS($A:A))))

Now, select A13:E13 then copy down to enough cells to account for all the
possible ties.

In the above formula, in the Offset function, the 4 and -4 are the number
of data columns in the table.

No error trapping/checking in the above formula. I used conditional
formatting to hide them.

Biff

 

[Dave Peterson]

Just to make your headache, the OP said: "could it be because my data is in the
range B241..F267?"

With no testing at all (even just glimpsing at those formulas makes me weak in
the knees), do you need to adjust anything? <vbg>

 

[Biff]

do you need to adjust anything? <vbg>

I can't do *ALL* the work!

Biff