MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional formulas with greater than and less than values


Posted by Kristi on October 01, 2001 5:52 AM

I am building a template to automatically calculate sales commissions. I need some help writing a formula that includes a range of percentages. For example if the salesperson makes over 50% Gross Profit on a job, he is paid 20% of the sales price. 40% to 50% he is paid 15%. Let's say the sales value is in cell A1 and the deciding Gross Profit % is in cell A2. The over 50% calculation is easy enough: IF(A2>50%, A1*.20, 0) However, I can't seem to get a formula to work for the condition if A2>40% but <49.99%.

Any ideas out there? Your help is greatly appreciated!

Thanks,
Kristi


Posted by Juan Pablo on October 01, 2001 5:58 AM

Use this one for example:

=(A2>=40%)*15%+(A2>=50%)*5%

Juan Pablo

Posted by Juan Pablo on October 01, 2001 6:00 AM

I forgot one thing, sorry

=(A2>=40%)*(A1*15%)+(A2>=50%)*(A1*5%)

Juan Pablo