Can I add another variable to my nested if/and formula?

[bromanbdc]

Ok, so here goes. There are 4 cells to this. B,H,I,M

B has two variables. Buy or Sell
H will be a number
I will be a number
M will say yes or no

Here are the stipulations:

If B="BUY", and I>=H, then "YES" should display in M.
If B="SELL", and I<=H, then "YES" should display in M.
If neither statement results in a yes, "NO" should be displayed.

I have been racking my brain, and have already had help with one column. I finished 90% of the sheet, just having a hard time with the last few pieces.

[kasan]

Hi,
Try this formula in cell M1

Please Login or Register  to view this content.

[thompson_djc]

Assuming the cell references are B, H, I and M and B can only hold the 2 specified values the formula in M could be:

If "BUY" then if H2>= I2 then YES, otherwise NO, otherwise (must be "SELL") so if I<= H then Yes otherwise NO

=IF(B2="BUY",IF(H2>=I2,"YES","NO"),IF(H2<-I2,"YES","NO"))

If you need to handle that B may be blank i.e neither BUY or SELL

=IF(B2="BUY",IF(H2>=I2,"YES","NO"),IF(B2="SELL",IF(H2<=I2,"YES","NO"),""))

[bromanbdc]

Thank You so very much!! I think that is the last piece of the puzzle. I really appreciate it!!

[bromanbdc]

Thompson, that is the same formula that I was trying. It would only work one way. Kasan's suggestion worked as needed. Thank you for your suggestion as well.

[bromanbdc]

ok, I thought the formula from Kasan was good, but I overlooked something. Another condition that needs to be added:

It needs to have data in order to calculate. As it is now, it is calculating with no numbers input into the I cell since
technically, no number is less than 0. Is there a way to update the formula to operate as it does, with the only added
action that if there are no numbers in the I field, M says "NO" as well? Kasan, this is the formula that I am referring to:

=IF(AND(B1="BUY",I1>=H1),"Yes",IF(AND(B1="SELL",I1<=H1),"Yes","No"))

[FDibbins]

I would adjust this...
=IF(AND(B1="BUY",I1>=H1),"Yes",IF(AND(B1="SELL",I1<=H1),"Yes","No"))
to this...
=IF(OR(AND(B1="BUY",I1>=H1),AND(B1="SELL",I1<=H1)),"Yes","No")

Now, not sure which cell you want to test for, but I will use B1 - adjust as needed...
=if(B1="","No",IF(OR(AND(B1="BUY",I1>=H1),AND(B1="SELL",I1<=H1)),"Yes","No"))

Or, swapped around...
=if(or(B1="",AND(B1<>"BUY",I1<H1),AND(B1<>"SELL",I1>H1)),"No","Yes")

[bromanbdc]

Ford, Thank you very much. Your suggestion worked flawlessly with one minor change:

=if(I1="","No",IF(OR(AND(B1="BUY",I1>=H1),AND(B1="SELL",I1<=H1)),"Yes","No"))

So the only change to yours was changing the first if function B1 to I1. I am sure that is what you meant though. I have now tested it for all conditions and it works flawlessly. Thanks again. Don't know what I would do without you all. Very helpful.