Forum Discussion

ssmullins99's avatar
ssmullins99
Copper Contributor
Aug 11, 2023

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 ...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Aug 11, 2023

ssmullins99 

 

=IF(L57-L56<0, 0, IF(L57-L56<30, 500+250*QUOTIENT(L57-L56, 5), 100*(L57-L56)))

ssmullins99's avatar
ssmullins99
Copper Contributor
Aug 11, 2023

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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 12, 2023

    ssmullins99 

    You must have used a different formula - the one that I posted returns 3000 for 30 over goal:

    • ssmullins99's avatar
      ssmullins99
      Copper Contributor
      Aug 12, 2023

      HansVogelaarif it works for you the obviously it’s an error in my side. I’ll go back and double check. Appreciate your help!

       

Resources