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" wrote:
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.

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

PS....

"Rumy" wrote:
I have dollar amounts in A,B,C,D,E,F columns.

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" <joeu2004 wrote in message
...
"Rumy" wrote:
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.

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

=IF(SUM(A2:C2)=0,IF(D2=0,SUM(E2:F2),D2),SUM(A2:C2) )
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Rumy" wrote:

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.

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

"Rumy" wrote:

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.

Thank you both Luke and Minty Fresh :) That was a great help.

"Minty Fresh" wrote:

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

"Rumy" wrote:

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.