Conditional Formatting "Less Than" formula on multiple cells ignoring "0" value

[Brendon Donald]

Hi,

I am looking for a conditional formatting formula which compares one cell value (CB51) against 6 other cells values (CB30, CB31, CB41, CB43, CB66 and CB69) to determine whether it is less than any of the 6 cells values and turns green but also ignores if any of the 6 cells values that are 0?

This is what I have and it doesn't work????
=AND(ISBLANK($CB$41)=FALSE,ISBLANK($CB$43)=FALSE,ISBLANK($CB$66)=FALSE,ISBLANK($CB$69)=FALSE,$CB$51<$CB$30,$CB$51<$CB$31,$CB$51<$CB$41,$CB$51<$CB$43,$CB$51<$CB$66,$CB$51<$CB$69)

Thanks,

 

[Craggs82]

I've tried your formula and it works for me. Are all the values in those ranges definitely numeric values?

 

[Fluff]

Do any of those cells contain a formula?

 

[Brendon Donald]

Do any of those cells contain a formula?

Yes they do (the formula for CB31 is =BZ31+CA31) which has the same logic for each of the 6 cells listed above, how can I fix this?

 

[Fluff]

In that case none of the cells are blank, so this part of your formula is doing nothing ISBLANK($CB$41)=FALSE,ISBLANK($CB$43)=FALSE,ISBLANK($CB$66)=FALSE,ISBLANK($CB$69)=FALSE
If you want to check for a 0 use $CB$41<>0

 

[Brendon Donald]

In that case none of the cells are blank, so this part of your formula is doing nothing ISBLANK($CB$41)=FALSE,ISBLANK($CB$43)=FALSE,ISBLANK($CB$66)=FALSE,ISBLANK($CB$69)=FALSE
If you want to check for a 0 use $CB$41<>0

Thanks Fluff, so is this how you would write the formula as CB30 and CB31 will always have values and only CB69, CB51, CB41, CB43 could have 0?
=AND($CB$41<>0)=FALSE,($CB$43<>0)=FALSE,($CB$66<>0)=FALSE,($CB$69<>0)=FALSE,$CB$51<$CB$30,$CB$51<$CB$31,$CB$51<$CB$41,$CB$51<$CB$43,$CB$51<$CB$66,$CB$51<$CB$69)

 

[Fluff]

No it should be like

=AND($CB$41<>0,$CB$43<>0,$CB$66<>0,etc)

 

[Brendon Donald]

No it should be like

=AND($CB$41<>0,$CB$43<>0,$CB$66<>0,etc)

Thanks Fluff, I'll give that ago later and report back tomorrow, thanks again for all your help.

 

[Fluff]

Ok

 

[Brendon Donald]

Ok

Hi Fluff, I have tried again with this new formula

=AND($CB$41<>0,$CB$43<>0,$CB$66<>0,$CB$69<>0,$CB$51<$CB$30,$CB$51<$CB$31,$CB$51<$CB$41,$CB$51<$CB$43,$CB$51<$CB$66,$CB$51<$CB$69)

But the rule still doesn't work..... what am I'm missing?