Conditional formatting is highlighting empty cells that contain formula

[rikkyshh]

Hi guys

I am using conditional formatting on cells which show % growth YoY for sales figures

The formula in those cells is basically IFERROR((F38-C38)/C38,"")

So any cells which give an error are blank

When applying conditional formatting, i am using Green for IF J38>0,
Red for IF J38<0

But those cells which are returining "" are also being highlighted green.

I cant figure out why and its so annoying

[Pepe Le Mokko]

As the cells contain a null text string, XL finds it larger than 0 as it first does a TYPE() comparison and when the content is text TYPE returns 2 which is larger than 1 ( TYPE value for number)
To XL text is always larger than any number
Check the " Stop if true" box after the IFERROR part
or use =AND(J38>0,J38<>"") for green

[Pepe Le Mokko]

BTW are you still using XL2003 ( I suspect not as you are using IFERROR)? If not update your profile as answers are tailored to the XL version you are using

[Greg M]

Hi there,

Using the following formula for conditional formatting works for me:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

[Pepe Le Mokko]

Thanks for updating your profile