Nested IF(OR Formula not calculating

**djvybz** · 03-26-2013, 09:48 AM

Hello Forum! I'm trying to create a sales tracking worksheet that will calculate a commission that is to be paid if the rep meets one of two variables, i.e., if they sell either 10 policies or $15000 total, they will receive $300 for that week. If they sell 15 or $20000, $500, etc. I created the formula:

=IF(OR(J18<10,J17<15000),0, IF(OR(J18<15,J17<20000),300, IF(OR(J18<20,17<25000),500, IF(OR(J18<25,J17<30000),800))))

But will calculate based on the policy count, but not on the premium. What am I doing wrong? See pic below.

Thanks for your help!!!

**Melvinrobb** · 03-26-2013, 09:59 AM

Is the problem that you simply didn't have the J in a part of the formula?

=IF(OR(J18<10,J17<15000),0, IF(OR(J18<15,J17<20000),300, IF(OR(J18<20,J17<25000),500, IF(OR(J18<25,J17<30000),800))))

**djvybz** · 03-26-2013, 10:14 AM

Thank you for replying! My excitment was very shortlived when I found, unfortunately, even with the J in the formula, it still is not calculating. I've included the excel file as an attachment if that helps anyone. Thanks again!

**Ace_XL** · 03-26-2013, 10:47 AM

Your logic is based on salesperson 'failing to meet' either of the criteria rather than 'meeting' either of the crtiteria.

Try something on these lines

=IF(OR(J18>25,J17>30000),1500,IF(OR(J18>20,J17>25000),800,IF(OR(J18>15,J17>20000),500,IF(OR(J18>10,J17>15000),300,0))))

Amend highlighted number as per requirement

**djvybz** · 03-26-2013, 11:49 AM

Originally Posted by Ace_XL

Your logic is based on salesperson 'failing to meet' either of the criteria rather than 'meeting' either of the crtiteria.

Try something on these lines

=IF(OR(J18>25,J17>30000),1500,IF(OR(J18>20,J17>25000),800,IF(OR(J18>15,J17>20000),500,IF(OR(J18>10,J17>15000),300,0))))

Amend highlighted number as per requirement

PERFECT!!! Thank you so much! SOLVED! Thanks for everyone's input!