Conditional formula(s) & labelling
 

I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

Conditional formula(s) & labelling
 

I am not sure this is what you need, but we can find the max with MAX(); then
we locate the column with MATCH(); then we find the color with OFFSET():

=OFFSET(A1,0,MATCH(MAX(2:2),2:2,0)-1)

--
Gary''s Student - gsnu200786


"MNF" wrote:

I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

Conditional formula(s) & labelling
 

I'm assuming you want something like this:

12...Green
14...Brown
15...Pink
16...Yellow

Enter this formula in G1:

=SMALL(A$2:D$2,ROWS(G$1:G1))

Enter this formula in H1:

=INDEX(A$1:D$1,MATCH(G1,A$2:D$2,0))

Select both G1 and H1 and copy down to G4:H4

If there are any duplicate numbers this will not work properly and a
different approach will be needed.

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

Conditional formula(s) & labelling
 

Thanks very much. Your suggestion has sorted it out.

"T. Valko" wrote:

I'm assuming you want something like this:

12...Green
14...Brown
15...Pink
16...Yellow

Enter this formula in G1:

=SMALL(A$2:D$2,ROWS(G$1:G1))

Enter this formula in H1:

=INDEX(A$1:D$1,MATCH(G1,A$2:D$2,0))

Select both G1 and H1 and copy down to G4:H4

If there are any duplicate numbers this will not work properly and a
different approach will be needed.

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I have
looked at/tried: =If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.

Conditional formula(s) & labelling
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
Thanks very much. Your suggestion has sorted it out.

"T. Valko" wrote:

I'm assuming you want something like this:

12...Green
14...Brown
15...Pink
16...Yellow

Enter this formula in G1:

=SMALL(A$2:D$2,ROWS(G$1:G1))

Enter this formula in H1:

=INDEX(A$1:D$1,MATCH(G1,A$2:D$2,0))

Select both G1 and H1 and copy down to G4:H4

If there are any duplicate numbers this will not work properly and a
different approach will be needed.

--
Biff
Microsoft Excel MVP


"MNF" wrote in message
...
I m using Excel 2002. My data looks like this:

a b c d
1 yellow Green Brown pink
2 16 12 14 15

I have used =max and =large formuli in another sheet (same workbook) to
display data in ascending order in a different table.

I also want to 'label' the data but can't work it out i.e. labelling 16
'yellow' using/adapting the above formuli. Also, in a seperate cell I
have
looked at/tried:
=If(a2b2,"yellow",if(a2c2,"yellow",if(a2d2,"yel low")))

Which works fine provided yellow is always the greatest value but get
an
error when I include the other columns in the same formula i.e. after
if(a2d2,"yellow",if(b12a2,"green" etc etc.

Can anyone help resolve this? Thanks.