Results 1 to 7 of 7

Thread: Tiered Charges

  1. #1
    New Lounger
    Join Date
    Oct 2009
    Location
    Essex UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    Could anyone suggest a simple 'one cell' formula that could calculate the following stepped pricing problem.

    Eg
    The quantity purchased falls between two ranges (Lower & Upper ranges), but it is not a straight lookup

    Lower Upper Price
    1 10000 0.10
    10001 20000 0.08
    20001 50000 0.07
    50001 80000 0.55
    80001 100000 0.35
    100000+ 0.25

    If the buyer purchases 25000 units, the first 10000 are at 0.1, the next 10000 are at 0.08 and then the next 5000 are at 0.07.
    The total cost is therefore :- 1000+800+350 = 2150

    Thanks

    Rob

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I devised a formula and a set of broken down formulas to show how it works, see attached.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached workbook. I used a table with some auxiliary results; someone else will probably post a formula that doesn't need such a table.

    [attachment=86402:Cost.xls]

    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Oct 2009
    Location
    Essex UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Guys,

    I was working on silmilar lines.
    I also couldn't do it without adding at least one additional column for the tiered range.

    However, I did discover the "Sumproduct" formula, which helps. Still, my formula is so complex, it seems hardly worth trying to get it down to one cell.

    thanks again

    Rob[attachment=86404:Tiered Pricing.xls]
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here's a version that uses only one auxiliary column: the cumulative price for each tier.

    [attachment=86405:Cost.xls]
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Oct 2009
    Location
    Essex UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's much more concise !

    Thanks again


    Rob

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I've added a single formula to a slightly modified version of JKP's workbook.
    FWIW.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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