IF with AND and OR
 

My data looks like so:

ColA ColB ColC ColD ColE
Product Type Price1 Price2 Profit/Loss
Sell A 38.43 39.47 1.04
Buy A 38.43 39.47 -1.04
Sell B 38.43 39.47 -1.04
Buy B 39.42 39.47 0.05

I would like a formula in ColE (Profit/Loss) to follow this logic:

If Sell and A then D-C OR if Buy and A then C-D OR if Sell and B then C-D OR
if Buy and B then D-C

I've tried a few combo's but could not get the results as shown in the table
above.

Thank you in advance.

=IF(OR(A2="Sell A",A2="Buy B"),D2-C2,C2-D2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" wrote in message
...
My data looks like so:

ColA ColB ColC ColD ColE
Product Type Price1 Price2 Profit/Loss
Sell A 38.43 39.47 1.04
Buy A 38.43 39.47 -1.04
Sell B 38.43 39.47 -1.04
Buy B 39.42 39.47 0.05

I would like a formula in ColE (Profit/Loss) to follow this logic:

If Sell and A then D-C OR if Buy and A then C-D OR if Sell and B then C-D
OR
if Buy and B then D-C

I've tried a few combo's but could not get the results as shown in the
table
above.

Thank you in advance.

One way:

=IF(OR(AND(A2="Sell",B2="A"),AND(A2="Buy",B2="B")) ,D-C,C-D)

In article ,
"carl" wrote:

My data looks like so:

ColA ColB ColC ColD ColE
Product Type Price1 Price2 Profit/Loss
Sell A 38.43 39.47 1.04
Buy A 38.43 39.47 -1.04
Sell B 38.43 39.47 -1.04
Buy B 39.42 39.47 0.05

I would like a formula in ColE (Profit/Loss) to follow this logic:

If Sell and A then D-C OR if Buy and A then C-D OR if Sell and B then C-D OR
if Buy and B then D-C

I've tried a few combo's but could not get the results as shown in the table
above.

Thank you in advance.

This is correct, but he's asking you to combine columns a and b into 1
column. If you dont combine, it would be ...

=IF(OR(and(A2="Sell",B2="A"),and(A2="Buy",B2="B")) ,D2-C2,C2-D2)

Dave

"Bob Phillips" wrote:

=IF(OR(A2="Sell A",A2="Buy B"),D2-C2,C2-D2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"carl" wrote in message
...
My data looks like so:

ColA ColB ColC ColD ColE
Product Type Price1 Price2 Profit/Loss
Sell A 38.43 39.47 1.04
Buy A 38.43 39.47 -1.04
Sell B 38.43 39.47 -1.04
Buy B 39.42 39.47 0.05

I would like a formula in ColE (Profit/Loss) to follow this logic:

If Sell and A then D-C OR if Buy and A then C-D OR if Sell and B then C-D
OR
if Buy and B then D-C

I've tried a few combo's but could not get the results as shown in the
table
above.

Thank you in advance.