Forum Discussion
Trouble with <>= in formula
I have been trying to put together a formula to calculate commissions that are on a tier that increases based on adding previous months gross margin to current months gross margin. We have 5 different tiers: 6%, 7%, 8%, 9%, 10%
| Monthly Gross Margin Dollars | Monthly Commission Tier |
| 0-19,999 | 6% |
| 20,000-39,999 | 7% |
| 40,000-59,999 | 8% |
| 60,000-79,999 | 9% |
| >80,000 | 10% |
This is the formula that I wrote:
=IF(SUM(B2:B5)=20000,0.06,IF(SUM(B2:B5)>=20000,0.07,IF(SUM(B2:B5)>=40000,0.08,IF(SUM(B2:B5)>=60000,0.09,IF(SUM(B2:B5)>=80000,0.1)))))
It works up until the total reaches 40000. It doesn't return the correct percentage after that. Can someone please help me to figure out where I am going wrong? Thank you!
4 Replies
Thanks Everyone! I finally figured out what I was doing wrong. The highest number needed to be at the front of the formula because once one of the criteria was met, it stopped looking at the rest of the formula. Thank you everyone for your time and input.
Let me clarify how commission is calculated. For example, Gross Margin is 45000.
When it is
45000 * 8%
or
20000*6% + 20000*7% + 5000*8%
- Hello
Use this formula
=IF(A1 >=20000,IF(A1>=40000,IF(A1>=60000,IF(A1>=80000,A1*0.1,A1*0.09),A1*0.08),A1*0.07),A1*0.06) - Change the first column of your gross margin table to these values (numbers!): 0 20,000 40,000 60,000 80,000 Then use a formula like this one (assuming your amount is in cell E3) to fetch the rate: =XLOOKUP(E3,$A$2:$A$6,$B$2:$B$6,,-1)
