Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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<"",B30<426),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(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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(B30<426,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<"",B30<426),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(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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(B30<426,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<"",B30<426),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(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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell Dropdown List. Display alternate text than found in list. | Excel Discussion (Misc queries) | |||
Compare text in 2 separate spreadsheets, when match found display | Excel Worksheet Functions | |||
how do I display filtered records found | Excel Discussion (Misc queries) | |||
Data Source Name Not Found | Excel Worksheet Functions | |||
how to display the count of lines found by autofilter? | Excel Discussion (Misc queries) |