using IF(ISBLANK with cells that contain equations returns #VALUE!

[osteolass]

Hi y'all
I have a function that I am trying to return a string value given certain conditions. Some of the referenced cells are "blank" so I am using the equation: IF(OR(ISBLANK(CQ2),ISBLANK(EM2)),"",IF(CQ2-EM2=-1,"up",IF(CQ2-EM2=0,"same",IF(CQ2-EM2>0,"down")))). This is returning #VALUE! when one or both of the referenced cells is "blank" but has an equation in it (that also uses ISBLANK logic).

I can't trim the worksheet of the functions because i'm still using them.

Any ideas that would resolve this and return a blank cell when one or both reference cells are blank? Is there a different logic command I should use? Have I just screwed up the equation (even though it's the same one that has worked before)

Appreciated!

[daddylonglegs]

Cells with "" aren't truly blank, so ISBLANK will return FALSE, try just comparing with "" like this

IF(OR(CQ2="",EM2=""),"",IF(CQ2-EM2=-1,"up",IF(CQ2-EM2=0,"same",IF(CQ2-EM2>0,"down"))))

[Jonmo1]

A formula returning "" is NOT Blank...It's a Null Text String.

Instead of ISBLANK(cell)
use
cell=""

[FDibbins]

IF(OR(CQ2="",EM2=""),"",IF(CQ2-EM2=-1,"up",IF(CQ2-EM2=0,"same",IF(CQ2-EM2>0,"down"))))

[osteolass]

Thanks y'all!

[FDibbins]

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

[daddylonglegs]

Another way to write your formula.....

=IF(COUNT(CQ2,EM2)=2,CHOOSE(SIGN(CQ2-EM2)+2,"up","same","down"),"")

[Tony Valko]

FWIW...

The ISBLANK function is not correctly named based on what it does.

It should have been named ISEMPTY.

[FDibbins]

good point Biff, you going to tell MS?
lol

[Tony Valko]

"Billions and Billions" of people have told MS!