IF/OR/AND function in a formula

  • Thread starter Thread starter Rumy
  • Start date Start date
R

Rumy

I have a unique scenario and need assiatance to create a formula.

I have dollar amounts in A,B,C,D,E,F columns. I need a result in G

it should be A+B+C. If A+B+C equals 0 the result should be D. If D also
equals 0 then the result should be E - F. Please help. Thanks.
 
Rumy said:
I have dollar amounts in A,B,C,D,E,F columns. I need a result in G[.]
it should be A+B+C. If A+B+C equals 0 the result should be D. If D
also equals 0 then the result should be E - F. Please help.
Click to expand...

=if(A1+B1+C1<>0, A1+B1+C1, if(D1=0, E1-F1, D1))
 
PS....

Rumy said:
I have dollar amounts in A,B,C,D,E,F columns.
Click to expand...

If you mean that you have dollar-and-cents amounts with mixed signs (some
positive, some negative), or if the "dollar" amounts are the result of
formulas formatted with zero decimal places, note that what appears to be
zero is not always exactly zero.

So it would be prudent to write:

=round(if(round(A1+B1+C1,2)<>0, A1+B1+C1, if(D1=0, E1-F1, D1)), 2)

Change D1=0 to ROUND(D1,2)=0 if you did not round the formula in D1.


----- original message -----

Joe User said:
Rumy said:
I have dollar amounts in A,B,C,D,E,F columns. I need a result in G[.]
it should be A+B+C. If A+B+C equals 0 the result should be D. If D
also equals 0 then the result should be E - F. Please help.
Click to expand...

=if(A1+B1+C1<>0, A1+B1+C1, if(D1=0, E1-F1, D1))
Click to expand...
 
Hi Rumy
This is what I got:
=IF(AND(SUM(A2:C2)=0,D2=0),E2-F2,IF(SUM(A2:C2)=0,D2,SUM(A2:C2)))

If I am reading the problem correctly,
Luke's formula is shorter and, therefore, more elegant, but it adds E and F,
rather than subtracting F from E.
With the following revision, I like Luke's better:
=IF(SUM(A2:C2)=0,IF(D2=0,E2-F2,D2),SUM(A2:C2))
The both test out the same.
M
 
Back
Top