| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=MAX-Return Cell info
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!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
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. Matthew wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
What if there are duplicate max values?
Biff "Matthew" wrote in message ... 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!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
Ooh. Excellent point.
I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
I'll await a reply from the OP!
Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
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:F2 67),COLUMN(C242:F267)-1)))} "Biff" wrote: I'll await a reply from the OP! Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
Could still do with some help on this guys !! Please!
"Matthew" wrote: 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:F2 67),COLUMN(C242:F267)-1)))} "Biff" wrote: I'll await a reply from the OP! Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
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 Matthew wrote: Could still do with some help on this guys !! Please! "Matthew" wrote: 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:F2 67),COLUMN(C242:F267)-1)))} "Biff" wrote: I'll await a reply from the OP! Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
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,MAT CH(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(IND EX($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" wrote in message ... 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 Matthew wrote: Could still do with some help on this guys !! Please! "Matthew" wrote: 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:F2 67),COLUMN(C242:F267)-1)))} "Biff" wrote: I'll await a reply from the OP! Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
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 "Biff" wrote in message ... 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,MAT CH(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(IND EX($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" wrote in message ... 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 Matthew wrote: Could still do with some help on this guys !! Please! "Matthew" wrote: 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:F2 67),COLUMN(C242:F267)-1)))} "Biff" wrote: I'll await a reply from the OP! Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
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 wrote: 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,MAT CH(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(IND EX($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" wrote in message ... 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 Matthew wrote: Could still do with some help on this guys !! Please! "Matthew" wrote: 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:F2 67),COLUMN(C242:F267)-1)))} "Biff" wrote: I'll await a reply from the OP! Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=MAX-Return Cell info
do you need to adjust anything? <vbg
I can't do *ALL* the work! Biff "Dave Peterson" wrote in message ... 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 wrote: 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,MAT CH(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(IND EX($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" wrote in message ... 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 Matthew wrote: Could still do with some help on this guys !! Please! "Matthew" wrote: 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:F2 67),COLUMN(C242:F267)-1)))} "Biff" wrote: I'll await a reply from the OP! Biff "Dave Peterson" wrote in message ... Ooh. Excellent point. I'll await one of your formulas! Biff wrote: What if there are duplicate max values? Biff "Matthew" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you make cell 2 return data if cell 1 contains text? | Excel Discussion (Misc queries) | |||
| If it's not a space, return the cell contents. | Excel Worksheet Functions | |||
| Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) | |||
| Find a value in a cell and return another value in a differant cel | Excel Discussion (Misc queries) | |||
| Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Linear Mode
