Excel function is returning undesired answer for blank cells

**dcwandj** · 10-28-2012, 06:57 AM

Hi! I'm trying to use the following function to test the contents of a cell and generate a code based on the value of less than, equal to or greater than zero. If there is no data in the cell then I want a a blank or error. It works ok when there is number data in the target cell, but when there's a blank I get a code and I don't understand why. This might be simple but I' missing it. I searched the forum and didn't find anything that I could easily relate to.

=IF(J2>0,"Incr",IF(J2=0,"NoChg",IF(J2<0,"Decr","Error")))

Thanks in advance D

**Pete_UK** · 10-28-2012, 07:18 AM

Try this:

=if(j2="","",IF(J2>0,"Incr",IF(J2=0,"NoChg",IF(J2<0,"Decr","Error"))))

Hope this helps.

Pete

**dcwandj** · 10-28-2012, 08:59 AM

Pete: Hey that did the trick! Thanks. If you have time, can you explain?

Derek

**Pete_UK** · 10-28-2012, 12:14 PM

Well, basically it checks first to see if J2 is a blank ("") and if so it returns a blank, but if not then it carries out your previous formula.

Hope this helps.

Pete