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

Occasional Visitor

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 



2 Replies

@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".