Conditional Format =max Function

J

JR573PUTT

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.
 
D

Dave Peterson

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.
 
B

Bob Phillips

=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)
 
J

JR573PUTT

DAVE,

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

Dave Peterson

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

JR573PUTT

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%
 
D

Dave Peterson

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.
 
J

JR573PUTT

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

The formula now works, thanks.
 
J

JR573PUTT

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

The formula now works, thanks.
 
J

JR573PUTT

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

The formula now works, thanks.
 
J

JR573PUTT

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

The formula now works, thanks.
 
Top