if statement returning only false condition value

[kaivalyap]

I am using windows 10 with an excel version MS office Home and Student 2016. When I use "if" statement with "AND" or nested if, or even only a simple "if", it is giving out result as only false condition value of the statement though the condition is true in the file. 

[#2 · Accepted solution]

@kaivalyap

In K16, you use a test like D16<I15<C16. Excel first evaluates D16<I15, resulting in TRUE or FALSE. Then, it evaluates if TRUE (or FALSE) is less than C16. That's where your formula breaks as it will always return FALSE. Now, you could turn a TRUE or a FALSE into a 1 or a 0, but then the outcome would most likely always be TRUE. To overcome this, you need to be specific when you write the formula and state that D15 must be less than I15 AND that I15 must be less than C16.

Now, I haven't tried to understand the logic for "buy" or "sell", but the attached formula returns all of the three possible outcomes. Up to you to decide if the applied logic is correct.

=IF(AND(D16<I15,I15<C16),"buy",IF(AND(D16<J15,J15<C16),"sell",""))

[#3]

@Riny_van_Eekelen 

Thanks a lot!!!! It worked. And by the way, it is a worksheet related to share market trading, and so there are terms "buy" and "sell" when i tried to automate the process.

Thanks again for your valuable correction.