Need Formula To Return Blank

[Barb]

Can anyone tell me why this formula will not return a
blank cell?

=IF(AND(J22>19),-10,IF(AND(J22<20,J22>15),0,IF(AND
(J22<16,J22>9),5,IF(AND(J22<10,J22>=0),10,""))))

I've also tried IF(ISBLANK(J22),"" but that doesn't work
either. I've used this in other formulas in this worksheet
with no problem, but this is the only formula where one of
the conditions actually returns a value of 0. It's when I
then try to clear that 0 from the cell that the blank
condition does not work.

Thanks.

 

[Jason Morin]

It won't return "" because you're covering the entire
spectrum of positive numbers, from 0 to
9.99999999999999E307. The first part covers everything
greater than 19, and the rest of the formula covers 0 to
19. In other words, any positive number you enter into J22
will always return -10,0,5, or 10.

Also, you don't need an AND in the part "AND(J22>19)". AND
is used when multiple conditionals must be met. Just use
=IF(J22>19,-10,....

HTH
Jason
Atlanta, GA

 

[SteveH]

Sorry I don't have a great answer for you, but as a workaround why dont you try putting that entire formula in a hidden cell for example AZ1. Then in the cell you are looking for the output use somthing simple such as

=if(AZ1=0,"",AZ1

That way you will get the correct value and if it is 0 the cell will be blank

Or you can use conditional formatting so that if the cell value is zero you can make the text white so that it can't be seen

It's not the answer you are looking for, but hopefully usable.