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

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" <[email protected]> a écrit dans le message
de news: [email protected]...
> 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

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" <[email protected]> a écrit dans le message
> de news: [email protected]...
> > 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
>
>
>

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

HTH
--
AP

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

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" <[email protected]> wrote in message
news:[email protected]...
> =SUBSTITUTE(ADDRESS(1,COLUMN(A1)+MATCH(MIN(A1:C1),A1:C1,0)-1,4);1;"")
>
> HTH
> --
> AP
>
> "mr_espresso" <[email protected]> a écrit dans le
> message de news: [email protected]...
>> 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" <[email protected]> a écrit dans le
>>> message
>>> de news: [email protected]...
>>> > 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
>>>
>>>
>>>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > =SUBSTITUTE(ADDRESS(1,COLUMN(A1)+MATCH(MIN(A1:C1),A1:C1,0)-1,4);1;"")
> >
> > HTH
> > --
> > AP
> >
> > "mr_espresso" <[email protected]> a écrit dans le
> > message de news: [email protected]...
> >> 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" <[email protected]> a écrit dans le
> >>> message
> >>> de news: [email protected]...
> >>> > 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
> >>>
> >>>
> >>>
> >
> >
>
>
>

If your data are in row 2, use the untested =3DMATCH(MIN(A2:C2),A2:C2,0). =
=20
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=20
the first 26 columns, use =3DCHAR(64+{above formula here w/o the =3D sign})

--=20
Regards,

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

In article <[email protected]>,=20
[email protected] says...
> Hi folks. For the sample data below, columns A =E2=A4=3D3F C contain surv=
ey response=20
> data: 3, 6 and 9. I'm looking for advice on writing a function for column=
D=20
> that would look at columns A =E2=A4=3D3F C and return the # of the column=
containing=20
> the min value (e.g. A in this example). Thank you in advance!
>=20
> A B C D
> 3 6 9 A
>=20

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 <[email protected]>,
>[email protected] 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

Hi Gord,

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

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

Option Explicit

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

--=20
Regards,

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

In article <[email protected]>, Gord Dibben=20
<gorddibbATshawDOTca> says...
> If you add this UDF to your workbook, you can return the column letter di=
rectly
> up to IV
>=20
> Function GetColLet(ColNumber As Integer) As String
> GetColLet =3D Left(Cells(1, ColNumber).Address(False, False), _
> 1 - (ColNumber > 26))
> End Function
>=20
> =3Dgetcollet(MATCH(MIN(2:2),2:2,0))
>=20
>=20
> Gord Dibben MS Excel MVP
>=20
>=20
> On Fri, 23 Jun 2006 09:18:31 -0400, Tushar Mehta
> <tm_200310@tushar_hyphen_mehta_dot_see_oh_em> wrote:
>=20
> >f your data are in row 2, use the untested =3DMATCH(MIN(A2:C2),A2:C2,0).=
=20
> >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=20
> >the first 26 columns, use =3DCHAR(64+{above formula here w/o the =3D sig=
n})
> >
> >--=20
> >Regards,
> >
> >Tushar Mehta
> >www.tushar-mehta.com
> >Multi-disciplinary business expertise
> > + Technology skills
> > =3D Optimal solution to your business problem
> >Recipient Microsoft MVP award 2000-2005
> >
> >In article <[email protected]>,=20
> >[email protected] says...
> >> Hi folks. For the sample data below, columns A =E2?=3D3F C contain sur=
vey response=20
> >> data: 3, 6 and 9. I'm looking for advice on writing a function for col=
umn D=20
> >> that would look at columns A =E2?=3D3F C and return the # of the colum=
n containing=20
> >> the min value (e.g. A in this example). Thank you in advance!
> >>=20
> >> A B C D
> >> 3 6 9 A
>=20
>=20

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