Formulas with a blank result

[vegan2]

I’m wondering if you could assist me with some formulas that I’m having trouble with.

Examples:

Cell D49: =IF(Input!C344=0,"",+Input!C344) (draws data from an input file)

Cell F49: =IF('[2002Budget.xls]Detail Budget'!$G$330=0,"",+'[2002Budget.xls]Detail Budget'!$G$330) (draws data from a budget file)

Cell H49: Here’s the problem cell

1.If Cell D49 ends up with a value posted, as well as F49, the simple formula D49-F49 works perfectly.
2.If Cell D49 and Cell F49 end up with no value (both are blank) I need to also post blank in H49 and =IF(ISERR(D43-F43)," ",D43-F43) works perfectly.
3.If D49 is blank and F49 has a value, the ISERR formula does not work with the same thing happening when F49 has a value and D49 is blank. I need to post, say if D49 is 100 and F49 is 0, 100 in H49, and if D49 is 0 and F49 is 100 H49 needs to be -100.

The problem is that one of the cells is blank, so a special formula is required to not produce a VALUE error.

Do you have a formula for H49 that would work regardless of values or no values in D49 and F49 – producing the required result or a blank in H49?

[Asala42]

G'day,

Cell H49: Here’s the problem cell

1.If Cell D49 ends up with a value posted, as well as F49, the simple formula D49-F49 works perfectly.
2.If Cell D49 and Cell F49 end up with no value (both are blank) I need to also post blank in H49 and =IF(ISERR(D43-F43)," ",D43-F43) works perfectly.
3.If D49 is blank and F49 has a value, the ISERR formula does not work with the same thing happening when F49 has a value and D49 is blank. I need to post, say if D49 is 100 and F49 is 0, 100 in H49, and if D49 is 0 and F49 is 100 H49 needs to be -100.

If I follow you ..How about:

=IF(OR(D49="",F49=""),"",D43-F43)

Adam

[Fryer Tuck]

Try a nested if/then:

= If(D49="",if(F49="","",-D49),if(F49="",D49,D49-F49))

[Aladin Akyurek]

On 2002-05-07 10:45, vegan2 wrote:
I’m wondering if you could assist me with some formulas that I’m having trouble with.

Examples:

Cell D49: =IF(Input!C344=0,"",+Input!C344) (draws data from an input file)

Cell F49: =IF('[2002Budget.xls]Detail Budget'!$G$330=0,"",+'[2002Budget.xls]Detail Budget'!$G$330) (draws data from a budget file)

Cell H49: Here’s the problem cell

1.If Cell D49 ends up with a value posted, as well as F49, the simple formula D49-F49 works perfectly.
2.If Cell D49 and Cell F49 end up with no value (both are blank) I need to also post blank in H49 and =IF(ISERR(D43-F43)," ",D43-F43) works perfectly.
3.If D49 is blank and F49 has a value, the ISERR formula does not work with the same thing happening when F49 has a value and D49 is blank. I need to post, say if D49 is 100 and F49 is 0, 100 in H49, and if D49 is 0 and F49 is 100 H49 needs to be -100.

The problem is that one of the cells is blank, so a special formula is required to not produce a VALUE error.

Do you have a formula for H49 that would work regardless of values or no values in D49 and F49 – producing the required result or a blank in H49?

It's not always a good idea if ever to return blanks in cells that participate/are used in further computations.

In D49 simply enter:

=Input!C344

In F49 simply enter:

='[2002Budget.xls]Detail Budget'!$G$330

If you don't see 0's, custom format the formula cell as

[=0]""

You can keep your current formula in H49, a bit amended, as simply:

=D43-F43

If you are not convinced by the foregoing, keep everyrhing as is and use in H49:

=SUM(D43,-F43)

Aladin