If(and) statements used with calculations

[jay-excel]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have an IF(AND... Statement that when FALSE, returns a blank cell. <o></o>
<o></o>
The trouble I am experiencing is using the cell with the IF(AND... statement in reference in a mathematical formula in another cell.<o></o>
<o></o>
Firstly, I want D17 to be blank if the condition is not met. =IF(AND(D12="yes",G12>0),G12,"")<o></o>
<o></o>
I want H16 to remain "0" if there is no math to do<o></o>
=(D17*E17)+(D18*E18)<o></o>
<o></o>
<o></o>
Currently the setup is:<o></o>
<o></o>
D17 --> =IF(AND(D12="yes",G12>0),G12,"")<o></o>
<o></o>
H16 --> =(D17*E17)+(D18*E18)<o></o>
<o></o>
If D17 is FALSE (displayed as a blank cell) then H16 returns "#VALUE"<o></o>
<o></o>
Is there a way to have D17 remain blank when D12 and G12 don't meet the condition and H16 continue to calculate the totals?<o></o>
<o></o>
<o></o>
Am open to all suggestions, even those that are vastly different from what I have attempted. <o></o>
<o></o>
Cheers<o></o>
<o></o>
- Jay<o></o>
<o></o>

 

[Splith]

Hmmm. Try custom formats. There are some guides online I will see if I can get some half baked format back to you soon.

 

[Splith]

Use a custom format. This should do the trick. Post the whole line into the custom format line.

0.00;0.00;""

this will display numbers from positive to negative to zero

123.45 -123,45

As you can see nothing will be displayed for a value of zero, but the value will remain.

 

[jay-excel]

Hello,

I tried changing the cell format to custom with your suggestion but was unsuccessful. The H16 cell still displays "#VALUE" when D17 is blank

 

[jay-excel]

Hi,

Sorry, you were right. It works graet, I had forgotten to correct the formula from

=IF(AND(D12="yes",G12>0),G12,"")


to

=IF(AND(D12="yes",G12>0),G12,)