• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting using criteria select which cells

polarisking

Member
Range A2:A6 contains an integer value
Range B2:B6 contains a blank or N value

The objective is to highlight the cell in Range A2:A6 having the minimum value, but ONLY using those cells in Col A where its corresponding Test value is blank. So, in this example, the result of the formula s/b 2 since the value of 1 is excluded based on a Test value of N.

Thanks in advance.

ValueTest
1N
2
3
4
5
 

polarisking

Member
Maybe, as Conditional Formatting rule
=MINIFS($A$2:$A$6;$B$2:$B$6;"")=$A2

or when that function is not available try
=MIN(IF($B$2:$B$6="";$A$2:$A$6))=$A2
What an impressive, straightforward solution. One change, the semicolon needs to be a comma, like so
=MIN(IF($B$2:$B$6="",$A$2:$A$6))=$A2

Thank you, so much!
 

GraH - Guido

Well-Known Member
Not on my end, I'm using Belgium settings. I normally change those when I copy solutions, here I simply forgot. ;-)
 

Peter Bartholomew

Well-Known Member
Precisely the same solution but slightly different in appearance:
The conditional format is based on the formula
=CF?
where CF? is defined to refer to
= MINIFS(Value, Test, "") = @Value

Moving back in time CF? might refer to
= MIN( IF(Test="",Value) )=(this Value)
where I have used 'this' to provide the intersection of the current row [ 7:7 or similar spreadsheet junk] and the column, 'value'.
 

pecoflyer

Active Member
What should be highlighted if the "blank" cells contain empty text strings ("") instead of being completely empty ??
As you posted an image (sheets are ALWAYS recommended) the contents of colB cannot be entirely defined.....
 
Top