+ Reply to Thread
Results 1 to 4 of 4

Can't Figure Out A Formula For Calculating My Alternating Commission Splits

  1. #1
    Registered User
    Join Date
    08-07-2022
    Location
    Kelowna, BC
    MS-Off Ver
    Google Sheets
    Posts
    2

    Question Can't Figure Out A Formula For Calculating My Alternating Commission Splits

    I'm trying to build a percentage calculation for my commission splits.

    For example, if I make $150,000 a year in commissions, my brokerage charges me 20% on the first $54,000 I earn. Then, they change me 6% on the next $50,000. Once I hit $104,000, they start charging me 1%.

    The best logical sentence I've come up with for this is the following. Unfortunately, I cannot for the life of me understand how this fits in an if/then equation. Please help.

    If X is less than or equal to 54000, then X x 20%.
    If X is less than or equal to 104000, then 54000 is charged at 20% + the remainder is charged at 6%.
    If X is greater than 104000, then 54000 is charged at 20% + 50000 charged at 6% + the remainder charged at 1%

    How can I put all of this into a single formula?

    Thanks.
    Michael

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Can't Figure Out A Formula For Calculating My Alternating Commission Splits

    If you were to use some helpers to create differential rates, count the rows, and formulate named ranges, then you could use a formula like this:

    Please Login or Register  to view this content.
    or this:

    Please Login or Register  to view this content.
    see the screenshot, and link to example file:

    https://docs.google.com/spreadsheets...gid=2100307022

    Screen Shot 2022-08-07 at 16.11.03.png
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    08-07-2022
    Location
    Kelowna, BC
    MS-Off Ver
    Google Sheets
    Posts
    2

    Re: Can't Figure Out A Formula For Calculating My Alternating Commission Splits

    Thank you.

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Can't Figure Out A Formula For Calculating My Alternating Commission Splits

    Hi sevenethics,

    If this has resolved your enquiry, please take a moment to mark it as SOLVED, by clicking "Thread Tools" in the menu right above your first post.

    Also, as you are new here, you may not know that you can thank those who have helped provide working solutions by clicking "* Add reputation" under their name.

    Have a great day!

+ 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] calculating 2 rate of commission by one formula
    By raysrains in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-13-2020, 10:15 AM
  2. Commission Calculating Formula HELP
    By borjasanz in forum Excel General
    Replies: 1
    Last Post: 02-18-2015, 11:39 PM
  3. IF/OR Formula for Calculating Commission
    By tig1142 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-06-2014, 10:03 AM
  4. [SOLVED] Commission Formula Calculating off Wrong Cell - Help!
    By COBBCITY in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-26-2013, 06:14 PM
  5. [SOLVED] Commission Splits- Sales between 2 levels
    By mginsburg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2013, 12:36 PM
  6. Calculating lap time splits
    By james-w-bray84 in forum Excel General
    Replies: 3
    Last Post: 05-24-2012, 09:54 AM
  7. Replies: 1
    Last Post: 10-17-2009, 08:59 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