Excel Help (Again)

[Adam]

Ok, so I have setup some basic conditional formatting rules as per the below picture

The aim of the CF is to highlight a cell in red in column BF if the value is greater or less than what's in column BC - this works fine

What I want to do is where yo can see red cells in column BF with a value of 2 and there is no value visible (it's technically zero but another CF rule hides the text) in a cell on the same row in column BC is not have the cell in column BF highlighted in red

I've done that I thought would solve it by adding in a 3rd CF rule based on the cell value that isn't visible on column BC as zero but that doesn't seem to work

Any suggestions

 

[andybond]

I believe it is seeing the 0 that isnt there , or I should say the null , as a null.

See if there is a CF rule for marking a cell if it is null , or ="" rather than a 0.

 

[Adam]

Ok will take a look tomorrow

Thanks


Sent from my iPhone using Tapatalk

 

[Steven103]

I assume you did that using "format cell based on formula" with =IF(BF<BC,1,0) in which case you just add another rule to the same group of cells with a nearly identical formula:

=IF(BC1=0,1,0)

Having read your post again, if you want exactly what you have asked and for it only to work when BF=2 you'd need:

=IF(AND(BF1=2,BC=0),1,0)

There are all sorts of ways you could avoid having a visible 0 in BF but who knows what you're doing with the rest of the sheet, it might be really useful to have a value 0 in there.

 

[Adam]

No I did the CF using “Format only cells that contain” and then “Cell Value” with a less than and more than rule.

I’ll look at trying to use the formula options as per your instructions.

In terms of the 0 or null, cells in column BC are bringing over a number populated elsewhere within the spreadsheet. I’ve hidden the Null value to keep the spreadsheet noise down as I don’t want it looking cluttered


Sent from my iPhone using Tapatalk

 

[Adam]

No. Couldn’t get this working unfortunately


Sent from my iPhone using Tapatalk