Function to return # of column with min value in selected rows

[mr_espresso]

Hi folks. For the sample data below, columns A – C contain survey response
data: 3, 6 and 9. I'm looking for advice on writing a function for column D
that would look at columns A – C and return the # of the column containing
the min value (e.g. A in this example). Thank you in advance!

A B C D
3 6 9 A

 

[Ardus Petus]

Try this:
=ADDRESS(ROW(),COLUMN(A1)+MATCH(MIN(A1:C1),A1:C1,0)-1,4)

It wille return a cell address, like: A1

HTH

 

[mr_espresso]

Excellent! Thank you for that fast reply.

One follow-up question. Is it possible to remove the row number from the
result so only the column number is returned (e.g. A instead of A1)? This is
important because each column number is going to be relabeled in my stats
software and I'd like to avoid relabeling 1000’s of cell addresses!

Thanks again

 

[Ardus Petus]

=SUBSTITUTE(ADDRESS(1,COLUMN(A1)+MATCH(MIN(A1:C1),A1:C1,0)-1,4);1;"")

HTH

 

[Biff]

If the columns used are always only A, B or C:

=CHOOSE(MATCH(MIN(A1:C1),A1:C1,0),"A","B","C")

Biff

 

[mr_espresso]

Brilliant!

If the columns used are always only A, B or C:

=CHOOSE(MATCH(MIN(A1:C1),A1:C1,0),"A","B","C")

Biff

 

[Tushar Mehta]

If your data are in row 2, use the untested =MATCH(MIN(A2:C2),A2:C2,0).
This will return 1 for the 1st column, 2 for the 2nd, etc.

To convert that to the column letter, and as long as the result is in
the first 26 columns, use =CHAR(64+{above formula here w/o the = sign})

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Gord Dibben]

If you add this UDF to your workbook, you can return the column letter directly
up to IV

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

=getcollet(MATCH(MIN(2:2),2:2,0))


Gord Dibben MS Excel MVP

 

[Tushar Mehta]

Hi Gord,

The proposed UDF has two limitations. First, it requires an active
worksheet. Second, it wouldn't work with 2007.

The version below should work from 97 (assuming ConvertFormula was
available then) to 2007. Of course, I only have 2003 on the computer I
am currently using.

Option Explicit

Function ColumnChar(ColNumber As Integer) As String
Dim Temp As String
Temp = Application.ConvertFormula( _
"=R1C" & ColNumber, xlR1C1, xlA1, True)
ColumnChar = Mid(Temp, 3, _
Application.WorksheetFunction.Find("$", Temp, 3) - 3)
End Function
Sub testIt()
MsgBox ColumnChar(256)

End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Gord Dibben]

Thanks for the update Tushar.

I have gotten rid of my partition with Excel 97 so can't test anymore in that
version.


Gord

Hi Gord,

The proposed UDF has two limitations. First, it requires an active
worksheet. Second, it wouldn't work with 2007.

The version below should work from 97 (assuming ConvertFormula was
available then) to 2007. Of course, I only have 2003 on the computer I
am currently using.

Option Explicit

Function ColumnChar(ColNumber As Integer) As String
Dim Temp As String
Temp = Application.ConvertFormula( _
"=R1C" & ColNumber, xlR1C1, xlA1, True)
ColumnChar = Mid(Temp, 3, _
Application.WorksheetFunction.Find("$", Temp, 3) - 3)
End Function
Sub testIt()
MsgBox ColumnChar(256)

End Sub

Gord Dibben MS Excel MVP