Display zero is no data is found

[wcurtis]

I have an IF formula that displays a zero if the number falls between a set
of numbers.
=IF(B30>450,B30-450,IF(B30<426,B30-426,"0"))
But if no data is found in the reference cell then I want the cell the
formula is in to display 0, how do you do that?
Example would be that B30 is blank, I get a -426.

Thanks in advance.

 

[Sheeloo]

Blank cells are treated as 0 in comaprisons so you can have
=IF(B30>450,B30-450,IF(AND(B30<>"",B30<426),B30-426,"0"))

 

[Sheeloo]

Blank cells are treated as 0 in comaprisons so you can have
=IF(B30>450,B30-450,IF(AND(B30<>"",B30<426),B30-426,"0"))

 

[JBeaucaire]

That works, but it evaluates virtually the entire formula before it discovers
there's nothing to do. I would suggest starting with that test so it can
stop right away if there's no value:

=if(B30="",0,IF(B30>450,B30-450,IF(B30<426,B30-426,"0")))

 

[JBeaucaire]

That works, but it evaluates virtually the entire formula before it discovers
there's nothing to do. I would suggest starting with that test so it can
stop right away if there's no value:

=if(B30="",0,IF(B30>450,B30-450,IF(B30<426,B30-426,"0")))

 

[Sheeloo]

You have a point. However given the information in the post, we don't know
which condition occurs most often. If most of the numbers are more than 450
then it is better to check for it first...

Also when I reply to a post I try to provide an answer which requires
minimum changes to what the person seeking help has already tried so that it
is easier for him/her to understand. I do not try to provide the best/optimum
solution.

 

[Sheeloo]

You have a point. However given the information in the post, we don't know
which condition occurs most often. If most of the numbers are more than 450
then it is better to check for it first...

Also when I reply to a post I try to provide an answer which requires
minimum changes to what the person seeking help has already tried so that it
is easier for him/her to understand. I do not try to provide the best/optimum
solution.