Multiple IF with AND formula, but One of the options is not returning correct outcome

Hi All,

Hope someone can help, I really cannot see where I am going wrong here.

I am trying to get a score of either "2", "1", or "0" in column C, depending on entry in both columns A and B, as below:

If A=Long, and B=Uptrend, then C=2
If A=Long, and B=Sidetrend, then C=1
If A=Long, and B=Downtrend, then C=0
If A=Short, and B=Uptrend, then C=0
If A=Short, and B=Sidetrend, then C=1
If A=Short, and B=Downtrend, then C=2

Using the following formula:

Formula:

Please Login or Register  to view this content.

I am getting the correct outcome for all, except: A=Short, and B=Downtrend, which is returning "0"

Any help would be appreciated as I am stumped.

Workbook attached.

Thanks in advance!

I did not look at your formula and wrote this:

=IF(B2="Sidetrend",1,IF(OR(AND(A2="Short",B2="Downtrend"),AND(A2="Long",B2="Uptrend")),2,0))

It gives the same wrong answer. Then I realised. There is a space after "Short " in your data validation that is throwing it out.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.