Conditional Format =max Function

I want to highlight the highest value in a row accross multiple columns, every other column contains the value I want to reference to obtain the MAX value:


A B C D E
S1 20 50% 30 70%
S2 30 60% 40 30%

I want to highlight the 70% because it is higher than the 20% and for store 2 I want to highlight the 60% because it is higher than 30%. I want to disregard the values in column B and D.

Try selecting C1:Cxx and hit ctrl while you select E1:Exxx (so that you have
both columns in the range selected).

Then with E1 the activecell
format|conditional formatting
Formula is:
=E1=MAX($C1,$E1)

Excel will adjust the column and row references for each cell in that selection.

Give it a nice shade.

JR573PUTT wrote:
>
> I want to highlight the highest value in a row accross multiple columns,
> every other column contains the value I want to reference to obtain the
> MAX value:
>
> A B C D E
> S1 20 50% 30 70%
> S2 30 60% 40 30%
>
> I want to highlight the 70% because it is higher than the 20% and for
> store 2 I want to highlight the 60% because it is higher than 30%. I
> want to disregard the values in column B and D.
>
> --
> JR573PUTT
> ------------------------------------------------------------------------
> JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=515862

--

Dave Peterson

=MAX(IF(MOD(COLUMN(B1:M1),2)=1,B1:M1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JR573PUTT" <[email protected]> wrote
in message news:[email protected]...
>
> I want to highlight the highest value in a row accross multiple columns,
> every other column contains the value I want to reference to obtain the
> MAX value:
>
>
> A B C D E
> S1 20 50% 30 70%
> S2 30 60% 40 30%
>
> I want to highlight the 70% because it is higher than the 20% and for
> store 2 I want to highlight the 60% because it is higher than 30%. I
> want to disregard the values in column B and D.
>
>
> --
> JR573PUTT
> ------------------------------------------------------------------------
> JR573PUTT's Profile:
http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=515862
>

DAVE,

The process worked for the first Row, and some random rows below, but not all rows?? Any thoughts?

It worked ok for me. Make sure you write the formula with respect to the
activecell.



JR573PUTT wrote:
>
> DAVE,
>
> The process worked for the first Row, and some random rows below, but
> not all rows?? Any thoughts?
>
> --
> JR573PUTT
> ------------------------------------------------------------------------
> JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=515862

--

Dave Peterson

Does not work, it highlighted every % in the last column except the last row which it highlighted the 1st column.


50 38% 56 60% 60 50%
60 50% 55 90% 88 86%
60 60% 22 20% 75 95%
50 29% 44 60% 32 220%
50 60% 66 50% 23 50%

I assume that this is now in A1:F5

I selected B1:B5
ctrlclick on D1:D5
ctrlclick on F1:F5

So F1 was my activecell.
format|conditional formatting
Formula is:
=F1=MAX($B1,$D1,$F1)

And I gave it a nice shade.

D1, D2, F3, F4 and B5 were shaded.



JR573PUTT wrote:
>
> Does not work, it highlighted every % in the last column except the last
> row which it highlighted the 1st column.
>
> 50 38% 56 60% 60 50%
> 60 50% 55 90% 88 86%
> 60 60% 22 20% 75 95%
> 50 29% 44 60% 32 220%
> 50 60% 66 50% 23 50%
>
> --
> JR573PUTT
> ------------------------------------------------------------------------
> JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=515862

--

Dave Peterson

Ok, I was enterting the ($first cel, $last cel) vs (first cel, second cel, third cell)..........

The formula now works, thanks.

Ok, I was enterting the ($first cel, $last cel) vs (first cel, second cel, third cell)..........

The formula now works, thanks.

Ok, I was enterting the ($first cel, $last cel) vs (first cel, second cel, third cell)..........

The formula now works, thanks.

Ok, I was enterting the ($first cel, $last cel) vs (first cel, second cel, third cell)..........

The formula now works, thanks.

You could have tried Bob's suggestion to avoid the clicking.

JR573PUTT wrote:
>
> Ok, I was enterting the ($first cel, $last cel) vs (first cel, second
> cel, third cell)..........
>
> The formula now works, thanks.
>
> --
> JR573PUTT
> ------------------------------------------------------------------------
> JR573PUTT's Profile: http://www.excelforum.com/member.php...o&userid=31587
> View this thread: http://www.excelforum.com/showthread...hreadid=515862

--

Dave Peterson