If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Display zero is no data is found
I have an IF formula that displays a zero if the number falls between a set
of numbers. =IF(B30450,B30-450,IF(B30426,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. |
#2
|
|||
|
|||
Display zero is no data is found
Blank cells are treated as 0 in comaprisons so you can have
=IF(B30450,B30-450,IF(AND(B30"",B30426),B30-426,"0")) "wcurtis" wrote: I have an IF formula that displays a zero if the number falls between a set of numbers. =IF(B30450,B30-450,IF(B30426,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. |
#3
|
|||
|
|||
Display zero is no data is found
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(B30450,B30-450,IF(B30426,B30-426,"0"))) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Sheeloo" wrote: Blank cells are treated as 0 in comaprisons so you can have =IF(B30450,B30-450,IF(AND(B30"",B30426),B30-426,"0")) "wcurtis" wrote: I have an IF formula that displays a zero if the number falls between a set of numbers. =IF(B30450,B30-450,IF(B30426,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. |
#4
|
|||
|
|||
Display zero is no data is found
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. "JBeaucaire" wrote: 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(B30450,B30-450,IF(B30426,B30-426,"0"))) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Sheeloo" wrote: Blank cells are treated as 0 in comaprisons so you can have =IF(B30450,B30-450,IF(AND(B30"",B30426),B30-426,"0")) "wcurtis" wrote: I have an IF formula that displays a zero if the number falls between a set of numbers. =IF(B30450,B30-450,IF(B30426,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. |
Thread Tools | |
Display Modes | |
|
|