Display zero is no data is found

W

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.
 
S

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"))
 
J

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")))
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top