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

**memories** · 10-27-2020, 06:42 AM

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.

**DocAElstein** · 10-27-2020, 12:53 PM

Hello memories,
Welcome to ExcelFox
Unless I am mistaken , I do not think anyone will be able to help you based on your short explanation and screenshot and attached file.
You have not made any attempt to explain what it is you want to be done
I have looked at your file and I don’t have the slightest idea what you are asking for.

Remember: You know your project. So all will be crystal clear to you.
We can’t read your mind.

It is very unlikely that anyone will have done exactly what you are doing.
So based on your short post, nobody is likely to have the slightest idea what you are doing, what you did manually, what should be done automatically and why , when etc.. etc…

At the very least, tell us what you did manually and why

Alan

**memories** · 10-27-2020, 02:54 PM

**DocAElstein** · 10-28-2020, 12:52 PM

Sorry, I spent an hour and can’t figure out what you want.
If no one else can help you here then maybe try at one of the more busy forums, such as excelforum.com or mrexcel.com
I doubt if writing the coding would be difficult. There is probably not much of a challenge for someone understanding basic VBA.
I think you will need to hope to find someone that has done something exactly like this and knows at a glance already what it is that you are actually doing.

Failing that you are going to need to take the time to walk through an example saying exactly what you did and where and why , etc....

I know it is going to all sound ridiculously obvious to you what you want, because you know what you want. So any explanation you give , you will understand.

You say you manually done it, “…. 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) …” .. but you have not said in detail exactly what steps you took.
Your explanations read to me like a quick summary. They would be useful to refresh and remind of the general idea of what’s going on, but not so much use to anyone trying to find out from scratch what you want.
The only thing I understand is the simple formula. I can’t figure out exactly how you came to put those figures in. And you are not saying if you actually want the macro to put those figures in or just give the final Sum results.
Maybe I could understand if I studied it for a few more hours, and then wrote a few codes doing slightly different things for you to choose from.... but if you want help in a free forum then I think you should make it more obvious what exactly you want, for someone who has no idea about what you are doing, or wanting to do.

Good luck

Alan

P.S: If you post the same question at other forums, then its useful to tell everybody everywhere where you have posted the same question