Commission Calculator ( % to target with variable % pay rate)

Hi there,

Would hugely appreciate some help from the community to assist with creating a formula to calculate commissions.

In a nut shell staff member is given a $ target. Staff get paid between 0%-5% of their actual sales result if they achieve at least 80% of set target.

eg.
$ result against $ target = 0-79% then pay $ result at 0%
$ result against $ target = 80-89% then pay $ result at 1%
$ result against $ target = 90-99% then pay $ result at 2%
$ result against $ target = 100-109% then pay $ result at 3%

up to 120% against target and up to 5% pay rate.

Tried LOOKUP function but for the life of me just can't get it to do what I need. Tutorial videos I found seem to do the reverse, by $ amount as opposed to % of target.

Thanks in advance!

Hi, thanks for your reply. Commission is paid on the total result.

Thank you! Exactly what I needed it to do. Cant thank you enough.