Brian White
Brian White

Reputation: 39

How can I use imbedded IF statements to include AND/OR to properly solve multiple conditions?

I have reviewed quite a few previous questions and answers in the forum, but none provide me the solution to solve my calculation needed.

This is a forex trading scenario in Excel where I am calculating movement between prices.

Here is the idea that I am trying to set a calculation for...

If p10="Win" and d10="Long", then ABS(n10-c10)*10000. This should return a positive number. If p10="Win" and d10="Short", then (c10-n10)*10000. This should also return a positive number.

If p10="Loss" and d10="Long", then (n10-c10)*10000. However, this should return a negative number. Just as if p10="Loss" and d10="Short), then (c10-n10)*10000 should also return a negative number.

I have tried combining IF(OR(AND statements, however, I am still not able to return proper results on all trades.

Would like to see if anyone may be able to see something or suggest a remedy that I may have not considered.

Upvotes: 0

Views: 59

Answers (1)

SQL Police
SQL Police

Reputation: 4206

Here is a solution. You need to put it into one single line:

=IF(AND(P10="Win"; D10="Long"); ABS(N10-C10)*10000; 
    IF(AND(P10="Win"; D10="Short"); (c10-n10)*10000; 
       IF(AND(P10="Loss"; D10="Long");  (n10-c10)*10000; 
          IF(AND(P10="Loss"; D10="Short"); (c10-n10)*10000; 
            )
         )
      )
    )

Upvotes: 0

Related Questions