+ Reply to Thread
Results 1 to 10 of 10

How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

  1. #1
    Registered User
    Join Date
    09-07-2021
    Location
    Phil
    MS-Off Ver
    2013
    Posts
    5

    Question How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Hi. This is my first time here so I'm not sure if I attached the excel sheet in the right way but I added a screenshot of it. I'm facing some challenging with calculating the commission and bonus with conditions in excel. I have explained some points below. I would really appreciate if someone can help me to complete the challenges with excel formulas or VB or both.

    Book1.PNG


    **Notes:**

    - Product ID is unique.
    - More Products will be added in the future.
    - Some products are under a main product (Sub-Product).
    - No. of Sub-Products are calculated automatically.
    Please Login or Register  to view this content.
    - Category Name is selected from a drop-down list from the right-side
    reference area.
    - Category Price is automatically pulled from the reference area based
    on the selection of the Category Name.
    Please Login or Register  to view this content.

    **Challenges:**

    - Commission should be calculated based on the Product Level and
    Category Name. Product Level depends on how many Products this
    Product has on top of it (e.g. Product1 has no Product on top of it
    (no main product) but Product5 has 4 products on top of it where
    Product4 on top of Product5 and Product3 on top of Product4 up to
    Product1) so the Product Level for Product5 is 4. Since Product5 is
    Category2 so the Category Commission for Product5 is $ 200 (From the
    reference area on the right side).

    - Bonus should be calculated based on the Category Name and must have
    atleast 2 Sub-Products with the same Category Name within 30 days as
    per the Initial Date (e.g. Product1 has 2 Sub-Products which are
    Product2 and Product9 (Both are Category1) but they are more than 30
    days difference in the Initial date so Bonus should be 0 (zero). e.g.
    Product2 has more than 2 Sub-Products all under the same Category
    Name and within 30 days so Bonus should be $ 20). I have tried the
    formula in the attached excel sheet
    Please Login or Register  to view this content.
    but couldn't complete it. Please advise with a formula with VB script which can do that.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Hello learn101 and Welcome to Excel Forum.
    It would seem that if product 5 is at level 4 then product 1 should be level zero, however there is no product level zero listed in column O so that I have a feeling I am missing something.
    Please manually fill in the correct values for the Commission and Bonus columns (H:I) so that we have something with which to compare the results of our proposed formulas/code.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    09-07-2021
    Location
    Phil
    MS-Off Ver
    2013
    Posts
    5

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Hello JeteMc and thank you very much for your reply.

    Apologies I mistakenly added additional zero to the commissions numbers those are deleted.

    Level zero has a different calculation which is not included in this sheet That's why I didn't mention it because the focus in this sheet is on everything after level zero.

    I manually filled Commission and Bonus columns (H:I) as requested (attached the new sheet). Also I used a color coding for the calculations under the table and changed few things to be able to explain it. Please let me know if you need further details from my side.

    Thank you.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    I am still confused, in fact perhaps a bit more so, about the commissions.
    Does the set up of the current file mean that O1:Q5 should no longer be considered?
    Based on what I think that I understand, I have added a column (F) to show the product level using: =IF(D2=0,0,INDEX(F$1:F1,MATCH(D2,A$1:A1,0))+1)
    The formula used in the commission column is: =SUMPRODUCT((F$18:F$21=F2)*(A$18:D$21))

  5. #5
    Registered User
    Join Date
    09-07-2021
    Location
    Phil
    MS-Off Ver
    2013
    Posts
    5

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Hello JeteM many thanks for your reply. I will use your sheet as my reference.

    I manually filled A18:D21 based on the details on O1:Q5. and these values are only to explain calculations. Seems like I confused you more I'm sorry for that.

    The formula you added =IF(D2=0,0,INDEX(F$1:F1,MATCH(D2,A$1:A1,0))+1) is perfectly correct. I think I need to add a top level with value "zero" in columns P and Q to match that formula.

    The formula you used in commission column is calculating the commission based on values I added in A18:D21. And it's repeating the commission as per the product level which made values in I9:I12 incorrect the correct values should be zeros.

    Commissions should be calculated based on product against values in Product ID, Product Level, Category Name, and Category Commissions (1 and 2). I'm using the "No. of Sub-Products" column to validate the commission because if the value is zero then I\m expecting a zero commission.

    Let me elaborate on how I manually calculated the $265 for Product1:

    Product 1 is the Main Product for Product2 and 9 (both are Level 1 for Product1).
    Product2 and 9 are Category1
    So, each will have a commission of $30 as per the cell "P2"
    So, the total commission of Level 1 products for "Product1" is $60 (which are $30 + $30)

    Now... Product9 is not a Main Product for any other products so there are no Level 2 products here related to Product 1. However, Product2 is the Main Product for Product3, Product10 and Product11. These 3 products are Level 1 for Product2 and also Level 2 for Product1.
    Product3, 10 and 11 are Category2
    So, each will have a commission of $40 as per the cell "Q3"
    So, the total commission of Level 2 products for "Product1" is $120 (which are $40 + $40 + $40)

    With the same sequence/steps you will see that total commission of Level 3 and 4 for "Product1" is $40 and $45.
    And the grand total commission of all Product Levels for Product1 will be $265 ($60 + $120 + $40 + $45).

    So, commission should not be repeated for the Product Level commission is a grand total once per Product. I usually verify that using the "E" column if value is zero it means the value is zero also for commission.

    Hopefully I was able to clarify things further. Thank you.
    Last edited by learn101; 09-18-2021 at 04:39 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Not sure how practical this would be for multiple main products, however it does provide the expected results for commissions for the example.
    Columns F:H display the products that are in the first product level for the respective row using: =IFERROR(INDEX($A$3:$A$12,AGGREGATE(15,6,(ROW($A$3:$A$12)-ROW($A$2))/($D$3:$D$12=$A2),COLUMNS($F2:F2))),"")
    Columns I:K display the categories for those products using: =IFERROR(INDEX($M$2:$M$12,MATCH(F2,$A$2:$A$12,0)),"")
    Columns Q:T display the amounts by product level using: =SUMPRODUCT(SUMIFS(INDEX($Z$3:$AA$6,COLUMNS($Q$2:Q$2),),$Z$2:$AA$2,INDEX($I2:$K12,COLUMNS($Q$2:Q$2),)))
    Note that any/all of the above columns may be moved and/or hidden for aesthetic purposes.
    Column O displays the total amount using: =SUM(Q2:T2)
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    09-07-2021
    Location
    Phil
    MS-Off Ver
    2013
    Posts
    5

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Firstly, thank you very much for the formulas I believe I can use them as a workaround. However, in this case I will need to manually add number of columns equal to the number of levels for each of F:H, I:K and Q:T and this either will be too long or I will have to limit the number of levels which will make me thin about additional formula to restrict the calculations of the commissions even if hide those columns.

    Is there a way to consolidate/merge the 3 formulas in F:H, I:K and Q:T into on formula to make it reduce the number of manual adding of columns or to even insert it direct to column “O” to avoid any manual steps and mistakes? I tried to merge them but failed to replace the values.

    I don't want to distract you focus about the first point but going back to my first post regarding the bonus, I couldn't add a condition to validate the that the number of days must be 30 days or less to apply the bonus. I tried to to us IF with VLOOKUP but I was not able to set the conditions as well as the formula structure in the right way.

    Please advise.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Perhaps someone will be able to consolidate the formulas for calculating commission, however I can't see a way to do that.
    As to the bonuses:
    1. Columns Z:AD display the dates of level 1 sub product categories appearing more than once in the row using: =IF(K2="","",IF(COUNTIFS($K2:$O2,K2)>1,VLOOKUP(F2,$A$2:$C$12,3,0),""))
    2. The formula for the Bonus column is: =IF(COUNTIFS(Z2:AD2,"<="&C2+30)<2,0,VLOOKUP(Q2,AF$3:AH$4,3))
    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    09-07-2021
    Location
    Phil
    MS-Off Ver
    2013
    Posts
    5

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    Hi. I tried to implement all what you advised me but in live production;

    I'm using:
    506 Product names
    8 Product Levels
    2 Category Names
    2 Category Prices
    2 Bonus Amounts

    I also added the below columns the same way you did:
    30 columns - Sub Products
    30 columns - Category
    8 columns - Levels
    30 columns - Dates For Calculating Bonus


    When I applied your formulas it worked fine in all new columns except the Levels columns which affected the final results in the commission column at the end.

    Also the formula in the final Bonus didn't work correctly.

    I made sure that I follow the exact setup and format you used in your formulas but failed to get the correct results and reviewed everything manytimes.

    I also noticed that some extra values are added and repeated in the levels new column .

    I'm sorry I can't share the file I'm using now because it's a production one but if I will refer to the last file you share here the incorrect values are in: S and T and U:X .

    Please guide me.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

    I'll try to help without seeing a sample of the file, however it may not be easy nor quick.
    Lets start with the Levels formula:
    1. What is the formula for Level 1 and what column is it in? What is the first row? (In the file attached to post #8 the formula is in column U starting in row 2)
    2. What is the range for the commissions? (In the file attached to post #8 the range is AI3:AK6 with the column headers in AI2:AK2)
    3. What columns are the categories in? (In the file attached to post #8 they are in columns K:O)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Re: IF Function to calculate bonus based on tiered bonus rates
    By plaza2154 in forum Excel General
    Replies: 9
    Last Post: 08-05-2020, 03:56 PM
  2. [SOLVED] bonus commission for every 10 sales
    By jitterbug888 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-07-2019, 11:53 PM
  3. [SOLVED] Formula to calculate tiered commission/bonus
    By shrijan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2017, 08:44 AM
  4. Replies: 2
    Last Post: 03-21-2017, 04:17 PM
  5. [SOLVED] calculate bonus at 30% of a figure, negative figures to be given 0 bonus.
    By jitterbug888 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-01-2015, 10:40 PM
  6. Nested if/and formulas in one cell to calculate bonus pay out
    By Vincentp302 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-03-2015, 12:50 PM
  7. Forecast Bonus Calculator - Require a formula to calculate the bonus' due
    By The_Snook in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2013, 07:32 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1