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 

Natalie_McMillan_0-1647650033175.png

 

2 Replies
=If(QtrSales>=$H$8,QtrSales*5%,0)

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