HELP - Calculation Based on Dynamic Variables (What-if Scenario)

[memories]

Hi,

I am a VBA beginner. What i am trying to do is to automate the allocation of the commission (Column AB3:AB17) based on Actual Sales.

Attached is an example for reference.
Column C is the actual sales and Row 2 is the target in the excel file.

Commission to be awarded only if the actual sales is between the target - for example, 1% of the amount between 60k & 69.99k and then 5% of the amount between 70k and 79.99k

Example Business Case Scenario

a) 1% Commission for achieving the 60K target (60K, 120K, 180K, ...)
b) 5% Commission for achieving the 70K target (70K, 140K, 210K, ...)
c) 9% Commission for achieving the 120K target (120K, 240K, 360K, ...)

If a sales order is 66.8K, the dealer gets a commission of 1%
If a sales order is 133.6K, the dealer gets a commission of 26% (where 1% from achieving the 60K Target and 25% for achieving the 120K target)

In the example file attached, I manually allocated the commission (Column D3 to AA15, taking reference from the 10x10 grid at D22:O16) based on the actual sales per order and a simple formula to calculate the total commission to be payout (Column AC3:AC17).

The manual effort is time-consuming and not error-proof.

Greatly appreciated if anyone can help.

Thank you.