IF Function with multiple True Statements

[Kearnsy]

Been on this for nearly three hours now researching and on youtube and pulling my hair out.

Trying to create an IF function for the following

Cell B3 the calculation within it is =A3-C3

However, i need to set up an If function whereby the following conditions have to be met so that B3 returns the correct answer. Conditions are as follows;

=If D3=0 then A3-C3
=If D3>0 then (A3-C3)-D3
=If E3=0 then A3-C3
=If E3>0 then (A3-C3)+E3

In summary, if cells D3 & E3 are empty then A3-C3, but if there is a value in D3 or E3 then carry out the normal calculation but either add or subtract the specific cell. Note both D3 & E3 will never have a value in both i.e. it will either be the following;

D3 & E3 empty
D3 with a value and E3 empty
or D3 Empty with E3 with a value

 

[bob33]

=IF(D3=0,A3-C3,IF(D3>0,(A3-C3)-D3,IF(E3=0,A3-C3,IF(E3>0,(A3-C3)+E3))))

Note: these statements are executed in the order presented so make sure your conditions make sense for the ordering of the conditions.

 

[Fluff]

As both D3 & E3 will never both have a value, how about

=A3-C3-D3+E3

 

[Kearnsy]

Hi All,

Thank you for the responses.

Bob - I struggled to make the IF function work as the True Value (test) kept overriding the false value (test). Therefore to work around it we did the following which worked;

IF(SUM(D3:E3)=0,A3-C3,IF(D3>0,(A3-C3)-D3,IF(E3=0,A3-C3,IF(E3>0,(A3-C3)+E3))))

However Fluff - Following your suggestion, we tried that also and it worked perfectly too, and we shortened the formula. I failed to spot to see it as a simple mathematical equation because as you say, both D3 & E3 will never both have a value.

Thank you both for your assistance.

 

[Fluff]

Glad we could help & thanks for the feedback.