=IF(AND Question

[Mike Slattery]

Hi:

Excel 2011 for Mac not happy with the following formula ;√(

=IF(AND(CC3>=-.85*CD3-CE3,CF3>=CD3-.85*CD3-CE3,1,0))

This is a simple test looking to count long tails at the close of the day for a stock.

CC3 = OPEN
CD3 = HIGH
CE3 = LOW
CF3 = CLOSE

Any suggestions?

Thanks, Michael

 

[circledchicken]

Hi,

You haven't provided much detail, but perhaps try something like:

=IF(
    AND(
      CC3 >= -0.85 * CD3 - CE3,
      CF3 >= CD3 - 0.85 * CD3 - CE3),
    1,
    0)

or

=--AND(
       CC3 >= -0.85 * CD3 - CE3,
       CF3 >= CD3 - 0.85 * CD3 - CE3)

 

[Mike Slattery]

Hi Circledchicken: Thanks for your help. I entered "=AND(CC3>=-0.85*CD3-CE3,CF3>= CD3-0.85*CD3-CE3,1,0)" into the cell. No error message was generated by excel this time but the value in the cell is "FALSE" not a 1 or a 0. When you add the "IF" you get a Parentheses needed error message.

Don't know what other information you might like but just let me know, glad to share.

Michael

 

[Mike Slattery]

Tried the code: It returns "1" in every instance?

Michael

 

[mbalho2]

=IF(AND(CC3>=-.85*CD3-CE3,CF3>=CD3-.85*CD3-CE3,1,0))

CC3 = OPEN
CD3 = HIGH
CE3 = LOW
CF3 = CLOSE

Your parentheses are in the wrong place if I understand your goal. Close the AND function after your two conditions. Now, if both conditions are true, it will return 1 and if one of the conditions is false, it will return 0.

=IF(AND(CC3>=-.85*CD3-CE3,CF3>=CD3-.85*CD3-CE3),1,0)

 

[circledchicken]

Hi Circledchicken: Thanks for your help. I entered "=AND(CC3>=-0.85*CD3-CE3,CF3>= CD3-0.85*CD3-CE3,1,0)" into the cell. No error message was generated by excel this time but the value in the cell is "FALSE" not a 1 or a 0.

Why did you enter that into the cell?? That's not what I posted in either option in post #2.
Also you need to ensure you copy the double negative at the front of the formula that converts TRUE and FALSE to 1 and 0 respectively.

Don't know what other information you might like but just let me know, glad to share.
Michael

Perhaps describe in words exactly how you intend your formula to work, and include sample data with the associated expected results.