+ Reply to Thread
Results 1 to 4 of 4

If formula for a paid percentage of the size of a job on a sliding table

  1. #1
    Registered User
    Join Date
    03-04-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365 pro
    Posts
    2

    If formula for a paid percentage of the size of a job on a sliding table

    Hi all
    My first post here. I'm looking for some formula help for the follow
    I have field techs who are paid a percentage of the size of a job on a sliding table. I'm trying to do this in a 1 cell calculation

    Between 0-2000 sq ft the tech receives a flat rate of 50
    between 2001-5500 the rate is .02 x the difference between 5500-2001
    between 5501 - 10000 the rate is .015 x the difference between 10000-5501
    above 10,000 the rate is .01 x the amount over 10000

    I can have the above details in their own cells and refer to them, but i'd like to have a formula in 1 line that will calculate a dollar amount based on a number from the cell
    These total amounts are imported from a scheduling software, and it reports 1 number. Hopefully that makes sense

    Here is an example of what I am trying to achieve

    12501 sq ft

    Formula to calculate
    Base rate $50
    2201-5500 is $65.98
    5501-10000 is $67.49
    10001+ is $25
    Total amount is $208.47

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: If formula for a paid percentage of the size of a job on a sliding table

    Hi and welcome
    the technique for this well known problem is described at http://www.mcgimpsey.com/excel/variablerate1.html
    or search the forum for " tiered commission"

  3. #3
    Registered User
    Join Date
    03-04-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365 pro
    Posts
    2

    Re: If formula for a paid percentage of the size of a job on a sliding table

    Great, thank you for the help Pepe

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: If formula for a paid percentage of the size of a job on a sliding table

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Percentage formula with changing range size
    By leahb909 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-29-2019, 06:13 PM
  2. Count selling price by sliding cost percentage
    By oeyandyprawira in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2016, 07:14 PM
  3. Percentage Formula Wrong and Pivot Table
    By hokkaido19 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-16-2015, 11:41 PM
  4. [SOLVED] Sliding Percentage Scale
    By allgeef in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-19-2014, 08:29 AM
  5. Creating a sliding percentage scale dependent on volume, affecting a rate
    By romanvid in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-11-2014, 12:19 PM
  6. [SOLVED] Percentage Commission on a sliding scale.
    By JonPFP in forum Excel General
    Replies: 6
    Last Post: 04-13-2006, 01:30 PM
  7. percentage:Threshold will be paid in proportion
    By Piggy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-18-2006, 05:55 AM

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