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

[memories]

Hi,

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

27353

Attached an example (done manually) for reference.
Greatly appreciated if anyone can help.
Thank you.

[Bob Phillips]

Are you just wanting to fill in the percentages in the grid?

[Paul_Hossler]

What do the order numbers represent (Col B)?

Are Sales (Col C) per order or cumulative?

What is the table in D22 used for, and how is it used?

The 10% in Col O has 1% + 9%. Where did those numbers come from?

'Target' in AE seems to be a VLookup in the D22 table 's first column to return Commission in col AF. What are the other 9 'targets???' in the table used for, i.e. the 2 - 10

What's the difference between the 'regular' and the 'dynamic' commissions?

Finally, what exactly do you mean by 'dynamic'? In a certain sense, all variables are dynamic, otherwise they wouldn't be variable

27357

[memories]

Not very good at describing my challenges - to award commission for each sales order based the commission table (AE3:AF17)

Column C is the actual sales per order 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

Additional Example Scenario

a) 1% Commission for achieving the 60K target (Sales order with amount of either 60K, 120K, 180K, ...)
b) 5% Commission for achieving the 70K target (Sales order with amount of either 70K, 140K, 210K, ...)
c) 9% Commission for achieving the 120K target (Sales order with amount of either 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.

[Bob Phillips]

For 133.6K, why isn't it 15%, 1% for > 60K, 5% for >70K, and 9% for > 120K? Do you actually want the commission percentage or the amount?

[Paul_Hossler]

Can you give us the COMPLETE process for 133,600?

Why 60, 000, 120,000, and 130,00 for targets?

Where does the 120,000, 240,000, and 260,000 get used?

It looks like (GREEN) that the Target 130,000 is from column 1 of the table, and the 8% is a VLookup of 130,000 from that table

Are you just looking for a way to get the 8%?


27375