Forum Discussion

Natalie_McMillan's avatar
Natalie_McMillan
Copper Contributor
Mar 19, 2022

how to calculate if amount is over or = too to add a percentage

HI All I am working on an assessment and the question is 

Calculate the Qtr Commission of the Qtr Sales Total using the % rate in H8

I have tried the VLOOKUP, SUMIF and IF calculations none are working, a hint was to use absolute reference 

Please help with correct formula 

 

Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 19, 2022

    Natalie_McMillan Can't be entirely sure, but I believe this is what you need.

     

    In the Qtr Commission column, enter:

    =MIN($G$3,[@[Qtr Sales Total]])*$H$3

    In the Sales exceeding Target column, enter:

    =MAX(0,[@[Qtr Sales Total]]-$G$3)

    In the Bonus column, enter:

    =[@[Sales exceeding Target]]*$I$3

    Attached a file with these formulae "at work". 

Resources