Forum Discussion
if statement returning only false condition value
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",""))
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",""))
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.
