simple MAX + comparison formula

[j0e]

hi

ok here's my question. i have 4 columns (A:D). for each row, i need the 5th cell to say which is the highest. lets say a-d are apples, oranges, bananas, pears. and the spread looked like this:

A O B P
5 7 3 5
1 8 2 4
1 0 6 4
8 2 6 3

i would want to add a 5th column that figures out which of A-D is highest and tells me. so it would look like this:

A O B P Max
5 7 3 5 O
1 8 2 4 O
1 0 6 4 B
8 2 6 3 A

i'm not worried about ties (they will be very rare) so you can have an error or give the wrong answer, whatever.

thanks very much for your help

[duane]

your data in a4:d8

this in cell e5 and copied down
through e8

=OFFSET($A$4,0,MATCH(MAX(A5:D5),A5:D5,0)-1,1,1)

[Barb Reinhardt]

If you have 5,7,3,5 in row 2 column A-D, enter the following in E2.

=INDIRECT("R1C"&MATCH(MAX(A2:D2),A2:D2,0),FALSE)

"j0e" <[email protected]> wrote in message
news:[email protected]...
>
> hi
>
> ok here's my question. i have 4 columns (A:D). for each row, i need the
> 5th cell to say which is the highest. lets say a-d are apples, oranges,
> bananas, pears. and the spread looked like this:
>
> A O B P
> 5 7 3 5
> 1 8 2 4
> 1 0 6 4
> 8 2 6 3
>
> i would want to add a 5th column that figures out which of A-D is
> highest and tells me. so it would look like this:
>
> A O B P Max
> 5 7 3 5 Oranges
> 1 8 2 4 Oranges
> 1 0 6 4 Bananas
> 8 2 6 3 Apples
>
> i'm not worried about ties (they will be very rare) so you can have an
> error or give the wrong answer, whatever.
>
> thanks very much for your help
>
>
> --
> j0e
> ------------------------------------------------------------------------
> j0e's Profile:
> http://www.excelforum.com/member.php...o&userid=28552
> View this thread: http://www.excelforum.com/showthread...hreadid=482105
>

[R.VENKATARAMAN]

suppose your data including column headings are in A1 to D5

type this in E2
=OFFSET($A$1,0,MATCH(MAX(A2:D2),A2:D2)-1,1,1)
copy to E3 to E5
your will get
O
O
B
P



"j0e" <[email protected]> wrote in message
news:[email protected]...
>
> hi
>
> ok here's my question. i have 4 columns (A:D). for each row, i need the
> 5th cell to say which is the highest. lets say a-d are apples, oranges,
> bananas, pears. and the spread looked like this:
>
> A O B P
> 5 7 3 5
> 1 8 2 4
> 1 0 6 4
> 8 2 6 3
>
> i would want to add a 5th column that figures out which of A-D is
> highest and tells me. so it would look like this:
>
> A O B P Max
> 5 7 3 5 Oranges
> 1 8 2 4 Oranges
> 1 0 6 4 Bananas
> 8 2 6 3 Apples
>
> i'm not worried about ties (they will be very rare) so you can have an
> error or give the wrong answer, whatever.
>
> thanks very much for your help
>
>
> --
> j0e
> ------------------------------------------------------------------------
> j0e's Profile:
http://www.excelforum.com/member.php...o&userid=28552
> View this thread: http://www.excelforum.com/showthread...hreadid=482105
>

[R.VENKATARAMAN]

mistake in my formula

it should be
=OFFSET($A$1,0,MATCH(MAX(A2:D2),A2:D2,0)-1,1,1)----------0 to be added at
the match function
apologies

"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
news:u#[email protected]...
> suppose your data including column headings are in A1 to D5
>
> type this in E2
> =OFFSET($A$1,0,MATCH(MAX(A2:D2),A2:D2)-1,1,1)
> copy to E3 to E5
> your will get
> O
> O
> B
> P
>
>
>
> "j0e" <[email protected]> wrote in message
> news:[email protected]...
> >
> > hi
> >
> > ok here's my question. i have 4 columns (A:D). for each row, i need the
> > 5th cell to say which is the highest. lets say a-d are apples, oranges,
> > bananas, pears. and the spread looked like this:
> >
> > A O B P
> > 5 7 3 5
> > 1 8 2 4
> > 1 0 6 4
> > 8 2 6 3
> >
> > i would want to add a 5th column that figures out which of A-D is
> > highest and tells me. so it would look like this:
> >
> > A O B P Max
> > 5 7 3 5 Oranges
> > 1 8 2 4 Oranges
> > 1 0 6 4 Bananas
> > 8 2 6 3 Apples
> >
> > i'm not worried about ties (they will be very rare) so you can have an
> > error or give the wrong answer, whatever.
> >
> > thanks very much for your help
> >
> >
> > --
> > j0e
> > ------------------------------------------------------------------------
> > j0e's Profile:
> http://www.excelforum.com/member.php...o&userid=28552
> > View this thread:
http://www.excelforum.com/showthread...hreadid=482105
> >
>
>

[j0e]

thank you very much, got it to work with your formula