EXCEL LEARNER,
Hello everyone, I am a new member. I am retired and I do exercises in Excel. I want to perform this calculation in the same instruction. Waiting for your contributions
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How to create Sliding Scale Commission Calculator in Excel
0
Answers
0
Selected Answer
Hello joean and welcome to the forum.
Creating a sliding scale is possible but more information is needed. First, what version of Excel are you using - different versions have diffeent capabilities. What will the range of the commission be? (ie: 0% - 3%, 0% -10%, 5%-15%) What conditions will affect the commission rate?
If you could upload a sample file to show what you are looking to achieve would be very helpful.We are willing to help where and when we can but will not build a file from beginning to end.
Cheers :-)
Discussion
Mnay Thanks
WINDOWS 11 OFFICE 365
WINDOWS 11 OFFICE 365
joean (rep: 2) Sep 13, '23 at 11:29 am
@Joean - that's not all that what Willie requested! Please edit your original question (to give sample rates and breakpoints etc) and use the Add Files... button (below the text) to upload a representative Excel file (without any personal data) to show your existing data.
Also, if you mark an Answer as Selected, it means your original question has been answered. It hasn't yet (so you can reverse that). I'll post an answer in a bit...
@Willie - you posted the above as an Answer but it should be a Discussion point under the user's question really.
Also, if you mark an Answer as Selected, it means your original question has been answered. It hasn't yet (so you can reverse that). I'll post an answer in a bit...
@Willie - you posted the above as an Answer but it should be a Discussion point under the user's question really.
John_Ru (rep: 6152) Sep 13, '23 at 11:38 am
Dears, I wish to knom how to build Excel commisions File using this attached document. My Pc runs Windows 11 and Execl office 365. With tables 1 Calcuation are mixed into percentage and fixed values. How doing tests. Their ares 2 files to build. One (2) with full percentage . The second to mix percentage ans fixed values. Idon't know how to attach tables. my email to be able to send you 2 tables. Thanks. desertfleuri@gmail.com
joean (rep: 2) Sep 13, '23 at 3:48 pm
Joean. Suggest you check my answer and file then edit your discussion point above to remove your email address (for privacy reasons and since we don't correspond directly using email).
If my Answer doesn't suit, I already told you to use the Add Files button to attach a representative Excel file (including your tables but no personal data)
If my Answer doesn't suit, I already told you to use the Add Files button to attach a representative Excel file (including your tables but no personal data)
John_Ru (rep: 6152) Sep 13, '23 at 3:55 pm
@John
Sorry for my mistake posting under answer instead of discussion. If there is a way to fix this let me know.
As usual, you have gone that extra step and created a file even though the poster has not made an attempt or provided the extra details. Good job.
Also, a curious note, in the response to my query, the poster states Windows 11 but the poster's profile lists Windows 7.
Sorry for my mistake posting under answer instead of discussion. If there is a way to fix this let me know.
As usual, you have gone that extra step and created a file even though the poster has not made an attempt or provided the extra details. Good job.
Also, a curious note, in the response to my query, the poster states Windows 11 but the poster's profile lists Windows 7.
WillieD24 (rep: 557) Sep 13, '23 at 6:36 pm
@Willie - no problem, I've done that several times (sometimes spotting it straight away and copying it , deleting then reposting under Discussion). Deleting the Answer also deletes any useful discussion under it so it's your decision to do or not. On Windows 7/11 there might be a mistake, the user might have two PCs or one with dual boot. I don't think the OS matters much in this case.
John_Ru (rep: 6152) Sep 14, '23 at 3:29 am
0
Hi Joean and welcome to the Forum.
In the attached file I've created a couple of tables to show how you might do it.
On worksheet "Commission", you'll see a table of values and commission rates (including two rows at the top and bottom to allow you optionally set a lower threshold - other than 0- for commission and/or a top ceiling). The yellow cells are the ones you need to adjust but you can insert extra rows (or type a row below) and the table will "expand to include it. For the sliding scale to work, the values in column A must be ascending so I've set data validation which prevents you putting a lower value in the next max value step.
This table is used in the table on the worksheet "Sales". Here the (table formula) in cell E2 is:
=VLOOKUP([@[Gross sales value]], Table1, 2)where the bit in bold means the row value under "Gross sales value" and is used by the VLOOKUP to find the rate in the table which I mentioned above (named Table1), in italics, and return the % value from column 2. I've used a VLOOKUP trick here- the default setting of VLOOKUP is to look for an approximate match (rather than exact) so it will give the rate for the band BELOW the sales value in column C. (This avoids "nested If" statements).
That rate (if not TBA) gets multiplied by the sales value in cell F2 by the formula:
=IFERROR([@[Commission rate]]*[@[Gross sales value]], "Refer to manager")where the bold IFERROR statement is wrapped around the multiplication. If the rate is TBA, you get the text in bold italic (which could be a maximum commssion value instead actually).
The real advantage of using a table here is that you only write the equations once (in the row below the headers) and other rows get that formula as the tabkle expands. E.g. just type in row 4 and the row will get the pale yellow cells (for you to edit) but the equations in columns E and F are there without any copying or effort on your side!
Hope this makes some sense and solves your problem. If so, please remember to mark this Answer as Selected.