Forum Discussion
IF AND OR THEN
I'm working on a commission spreadsheet and having problem with figuring the formula for the Bonus: where L56 is the monthly goal and L57 is total sales. It is a step up bonus plan. I have searched everywhere and can't figure it out. I'm trying to say if L57 (sales)=L56(goal) then 500, if not 0, OR L57(sales)>L56 AND L57<L56+10 then 750, Or L57>L56+9 AND L57<L56+15 then 1000 and so on.
Here is the actual Bonus Plan
Goal: $500
5-9 Over Goal: $750
10-14 Over Goal: 1,000
15-19 Over Goal: $1,250
20-24 Over Goal: $1,500
25-29 Over Goal: $1,750
30+ Over Goal: $3,000 + $100 for each additional Over Goal
this might be a long way around but it works: =IFS(L56-L57=0,500,L57-L56>=30,3000+100*(L57-L56-30),L57-L56>=25,1750,L57-L56>=20,1500,L57-L56>=15,1250,L57-L56>=10,1000,L57-L56>=5,750,L57-L56>0,500,L57<L56,0)
HansVogelaar Thank you , I never would've figured that out! Everything works perfect except when I put the sell in that makes it 30 over the goal it calculates the bonus as 4980 instead of 3000 but from there it does add 100 for each additional sale. It's just that 30 over it calculates wrong.
You must have used a different formula - the one that I posted returns 3000 for 30 over goal:
