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