If One Cell In A Row Is True, Every cell Comes Out As True.

[marlina]

I want to conditionally format some data. Let's say i want each cell to look if the row contains max value. so basically the each cell going to look 2 things:
1-If it is the biggest value in the data.
2-if the Row contains the biggest value in the data.

 

[marlina]

So i came up with this Formula:
=OR($A5=$G$5,$B5=$G$5,$C5=$G$5,$D5=$G$5,$E5=$G$5)
where G5 is LARGE(A5:E16,1)
if you guys have any better solution. please let me know.

 

[Aladin Akyurek]

In CF this might suffice...

=MATCH($G$5,$A5:$E5,0)

 

[marlina]

In CF this might suffice...

=MATCH($G$5,$A5:$E5,0)

yes match is also working! with ISNUMBER, thank you for your time. What if i have an AND criteria ?

 

[Aladin Akyurek]

yes match is also working! with ISNUMBER, thank you for your time. What if i have an AND criteria ?

In conditional formatting is ISNUMBER not needed.

What is the additional criterion you want to add?

 

[marlina]

In conditional formatting is ISNUMBER not needed.

What is the additional criterion you want to add?

Doesn't CF works with true and falses ?
i wanted a criteria like highlight a row if it multiple conditions. lets say all the cells are filled or the row contains two biggest numbers.
thank you for your reply.

 

[Aladin Akyurek]

Doesn't CF works with true and falses ?

Yes, it does.

MATCH returns either a non-zero number or #N/A. CF ignores #N/A and interprets a non-zero number as TRUE. Hence ISNUMBER round MATCH in CF omitted.

i wanted a criteria like highlight a row if it multiple conditions. lets say all the cells are filled or the row contains two biggest numbers.
thank you for your reply.

Let's take A1:C5

Select A1:C5.
Activate CF, the formula option.
Apply the following formula

=MATCH(TRUE,ISBLANK($A1:$C1),0)

which will pick out the whole record if that record contains any blank.

If you run the following formula

=COUNTIFS($A1:$C1,LARGE($A$1:$C$5,1))+COUNTIFS($A1:$C1,LARGE($A$1:$C$5,2))>=2

the records housing the instances of two largest values from A1:C5 will be picked out.

Hope this helps.
<sub><sup><strike>


</strike></sup></sub>​