Help with complex if then and conditional formula

[JNYEROGL]

I need to enhance this formula to account for another condition.

In English: if F7= Lease or Referral the result is -0- (end)
Next condition: If F7 is Buyer or Seller then it must look at P7 for commission amount and if the commission is less than 2000 the result is -0-, if the commission shown in P7 is greater than 2000, then I need result of ROUNDUP(N7/250000,0)*50)

=IF(OR(F7="Lease",F7="Referral"),0,IF(OR(F7="Buyer",F7="Seller"),(ROUNDUP(N7/250000,0)*50)))

[6StringJazzer]

=IF(OR(F7="Lease",F7="Referral"),0,IF(OR(F7="Buyer",F7="Seller"),IF(P7[lessthan]2000,0,ROUNDUP(N7/250000,0)*50)))

Note that you said less than 2000 and greater than 2000. This formula uses less than 2000 and greater than or equal to 2000. For [lessthan] use a less-than symbol The board seems to think I am trying to do an HTML injection here and won't allow it.

[JohnTopley]

Try

=IF(OR(F7="Lease",F7="Referral"),0,IF(AND(OR(F7="Buyer",F7="Seller"),P7>2000),(ROUNDUP(N7/250000,0)*50),0))

[JNYEROGL]

@JohnTopley....that formula did it. Thanks! However, I have one additional condition that I need to account for
IF(OR(F2="Lease",F2="Referral"),0,IF(AND(OR(F2="Buyer",F2="Seller"),P2>2000),100))

I now need to add it to the if/and statement that AH2 must also equal "yes" .... is that possible? I'm guessing it's another nested formula?

[protonLeah]

=IF(OR(F7="Lease",F7="Referral",P7 <= 2000),0,ROUNDUP((N7/250000)*50,0))

[JohnTopley]

This ... ??

IF(OR(F2="Lease",F2="Referral"),0,IF(AND(OR(F2="Buyer",F2="Seller),AH2="Yes",P2>2000),100))

[leelnich]

Or just:
=IF(AND(OR(F2="Buyer",F2="Seller"),AH2="Yes",P2>2000),100,0)

NOTE: John's formula (post #6) yields "False" if F2 does NOT equal ANY of the specified terms. This one yields 0.

The first formula can also be shortened in the same way:
IF(AND(OR(F7="Buyer",F7="Seller"),P7>=2000),ROUNDUP(N7/250000,0)*50,0)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[sandy666]

or
=IF(AND(OR(F2="Buyer",F2="Seller"),AH2="Yes",P2>2000),100,0)


=IF(AND(OR(F2={"Buyer","Seller"}),AH2="Yes",P2>2000),100,0)