Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-26-2020, 06:50 PM
memories memories is offline HELP - Calculation Based on Dynamic Variables (What-if Scenario) Windows 10 HELP - Calculation Based on Dynamic Variables (What-if Scenario) Office 2019
Novice
HELP - Calculation Based on Dynamic Variables (What-if Scenario)
 
Join Date: Oct 2020
Posts: 1
memories is on a distinguished road
Default HELP - Calculation Based on Dynamic Variables (What-if Scenario)

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
File Type: jpg Example.jpg (64.2 KB, 24 views)
Attached Files
File Type: xlsx Example.xlsx (45.4 KB, 4 views)

Last edited by memories; 10-27-2020 at 02:17 AM.
Reply With Quote
  #2  
Old 10-26-2020, 11:25 PM
Purfleet Purfleet is offline HELP - Calculation Based on Dynamic Variables (What-if Scenario) Windows 10 HELP - Calculation Based on Dynamic Variables (What-if Scenario) Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Cross posted on MOTH


https://www.myonlinetraininghub.com/...at-if-scenario
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Perform calculation on value above in the same column based on criteria DJ0691 Excel 2 01-14-2020 06:20 AM
Generate template-based letter with multiple variables shallaes Word VBA 5 07-12-2018 12:18 AM
HELP - Calculation Based on Dynamic Variables (What-if Scenario) Need a formula to set a field based on two variables unknownhere2000 Excel 2 01-12-2016 01:05 PM
HELP - Calculation Based on Dynamic Variables (What-if Scenario) Rules based due date calculation dlowrey Excel Programming 3 05-12-2013 08:30 PM
Calculating a cell value based on many variables malawimick Excel 0 12-14-2011 05:29 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft