Hi,
I am a VBA beginner, trying to build a solution to automate calculation based on dynamic variables.
Example.jpg
Attached an example (done manually) for reference.
Greatly appreciated if anyone can help.
Thank you.
Hi,
I am a VBA beginner, trying to build a solution to automate calculation based on dynamic variables.
Example.jpg
Attached an example (done manually) for reference.
Greatly appreciated if anyone can help.
Thank you.
Are you just wanting to fill in the percentages in the grid?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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.
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?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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%?
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Posting Permissions
Reply With Quote
