Hi,

I am a VBA beginner, trying to build a solution to automate calculation based on dynamic variables.

Attached an example for reference the commission reward table for each dealer.

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

If a sales order is 60K, the dealer gets a commission of 1%
If a sales order is 120K, 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 based on the sales order figure and a simple formula to calculate the total commission to be payout.
The manual effort is time-consuming and not error-proof.

I looking for a VBA solution to allocate the commission.




Greatly appreciated if anyone can help.

Thank you.

Attached Images

Example.jpg (64.2 KB, 24 views)

Attached Files

Example.xlsx (45.4 KB, 4 views)

Cross posted on MOTH


https://www.myonlinetraininghub.com/...at-if-scenario