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

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

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

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

It wille return a cell address, like: A1

HTH
--
AP

"mr_espresso" a écrit dans le message
de news: ...
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

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

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 1000s of cell addresses!

Thanks again

"Ardus Petus" wrote:

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

It wille return a cell address, like: A1

HTH
--
AP

"mr_espresso" a écrit dans le message
de news: ...
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

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

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

HTH
--
AP

"mr_espresso" a écrit dans le message
de news: ...
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" wrote:

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

It wille return a cell address, like: A1

HTH
--
AP

"mr_espresso" a écrit dans le
message
de news: ...
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

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

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

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

Biff

"Ardus Petus" wrote in message
...
=SUBSTITUTE(ADDRESS(1,COLUMN(A1)+MATCH(MIN(A1:C1), A1:C1,0)-1,4);1;"")

HTH
--
AP

"mr_espresso" a écrit dans le
message de news: ...
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" wrote:

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

It wille return a cell address, like: A1

HTH
--
AP

"mr_espresso" a écrit dans le
message
de news: ...
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

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

Brilliant!

"Biff" wrote:

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

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

Biff

"Ardus Petus" wrote in message
...
=SUBSTITUTE(ADDRESS(1,COLUMN(A1)+MATCH(MIN(A1:C1), A1:C1,0)-1,4);1;"")

HTH
--
AP

"mr_espresso" a écrit dans le
message de news: ...
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" wrote:

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

It wille return a cell address, like: A1

HTH
--
AP

"mr_espresso" a écrit dans le
message
de news: ...
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

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

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

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

A B C D
3 6 9 A

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

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


On Fri, 23 Jun 2006 09:18:31 -0400, Tushar Mehta
<tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote:

f 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

In article ,
says...
Hi folks. For the sample data below, columns A €=3F 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 €=3F 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

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

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

In article , Gord Dibben
<gorddibbATshawDOTca says...
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


On Fri, 23 Jun 2006 09:18:31 -0400, Tushar Mehta
<tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote:

f 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

In article ,
says...
Hi folks. For the sample data below, columns A ?=3F 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 ?=3F 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

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

Thanks for the update Tushar.

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


Gord

On Sat, 24 Jun 2006 14:14:34 -0400, Tushar Mehta
<tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote:

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