+ Reply to Thread
Results 1 to 8 of 8

Formula to calculate User Fees based on variable-rate table

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel for Mac 2011
    Posts
    15

    Formula to calculate User Fees based on variable-rate table

    I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)

    I've attached an example spreadsheet if that helps better illustrate what I'm looking for.

    Thanks a million for the help
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Formula to calculate User Fees based on variable-rate table

    May be this.....
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Formula to calculate User Fees based on variable-rate table

    Hello Cowboys9,

    What is the purpose of Column D for "Users" then?

    In the attached sample Workbook I have assumed that it is where you enter the number of users in the respective Tier catogories. If one would do that, then the actual total is different from what you regard as an obvious total of 2,100,000.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to calculate User Fees based on variable-rate table

    This may be of use. It calculates the number of users in each category and then applies the appropriate fee.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Formula to calculate User Fees based on variable-rate table

    @ newdoverman,

    The way I see it is that Column A is the dominant factor. If Column D is less than the Minimum stipulated in Column A, it should not be brought into the equation. As I did in my sample Workbook.

    Regards

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to calculate User Fees based on variable-rate table

    This demonstrates how a problem is written can result in differing interpretations.

    I interpreted the question as having cut-offs in prices at 1 million, 4 million, 7 million and over 7 million. In other words, from 0 to 1,000,000 paid at a rate of .4, from 1000001 to 4,000,000 at .3, from 4,000,001 to 7,000,000 at .2 and 7,000,001 and more at .1

    I completely understand your interpretation and in fact started solving using the same method but the total amount quoted was something that I couldn't achieve.

    It will be interesting to see what the interpretation was supposed to be....I'm not betting anything on either choice

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Formula to calculate User Fees based on variable-rate table

    @ newdoverman,

    Thank you for your feedback.

    I couldn't agree with you more, since I have also stated my confusion in my post.

    According to me, it should be either the one or the other, or a much clearer explanation of the parameters to be applied to obtain a certain desired outcome.

    Regards

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula to calculate User Fees based on variable-rate table

    I totally agree with you.

    I must have read this problem a dozen times and finally just made a choice of the direction that I was going to take...right or wrong.

+ 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. Replies: 6
    Last Post: 06-08-2012, 11:28 AM
  2. calculate fees from table
    By jcavigli in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-03-2008, 11:34 AM
  3. Designing a formula to calculate western Union fees and shipping costs
    By MASTERforge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2007, 03:29 PM
  4. how do I calculate a monthly payment based on a variable rate?
    By Chick N Egg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-17-2005, 05:10 PM
  5. How do I calculate interest and deduct late fees based on date pa.
    By leon in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-06-2005, 11:06 AM

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