+ Reply to Thread
Results 1 to 11 of 11

Excel Formula for Multi Tiered Pricing

  1. 02-02-2021, 02:31 PM #1
    daniellegardner1
    daniellegardner1 is offline
    Registered User
    Join Date
    02-02-2021
    Location
    Colorado, USA
    MS-Off Ver
    2019
    Posts
    5

    Lightbulb Excel Formula for Multi Tiered Pricing

    Hello everyone. I am new to this forum and to complex formulas in Excel, and am trying to calculate a formula using a multiple tiered pricing model. I have searched MANY posts and tried multiple formulas with no luck. I need to be able to input the amount of gallons used and have it calculate the total cost. If someone uses 18,000 gallons, it needs to charge the first 5000 gallons at $3/per 1000 gallons + the next 5000 gallons at $3.45/1000 gallons and so on. There is also a base charge regardless of any gallons used. My model looks like this:

    Charge Rate Cost Per 1000 gallons
    Base Charge $21.53
    0 to 5,000 gallons $3.00
    5,001 to 10,000 gallons $3.45
    10,001 to 15,000 gallons $3.97
    15,001 to 20,000 gallons $4.56
    20,001 to 25,000 gallons $5.25
    >25,001 gallons $6.03

    The formula I have had the most success with is below, however it doesn't seem to be adding the different tiers together and only calculating based on what tier the amount falls in. Using the above example, it will only calculate 18000 gallons at $4.56/1000 gallons, instead of the 2 previous tiers. Excel also tells me I have too many arguments in the string if I input all the tiers. Does anyone have ideas for a better formula, or help with why mine is not producing the correct answers? Thank you so much!

    =IF(AND(E3>0,E3<=5000),((E3-0)/1000)*3,IF(AND(E3>5001,E3<=10000),((E3-5001)/1000)*3.45,IF(AND(E3>10001,E3<=15000),((E3-10001)/1000)*3.97,IF(AND(E3>15001,E3<=20000),((E3-15001))/1000)*4.56)))+21.53
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-02-2021, 02:46 PM #2
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Excel Formula for Multi Tiered Pricing

    Try below.
    =SUMPRODUCT((F3>C5:C10)*((C5:C10+5000)<F3)*(B5:B10)*5)+SUMPRODUCT((F3>C5:C10)*((C5:C10+5000)>F3)*(B5:B10)*((F3-C5:C10)/1000))+B4

    EDIT:
    Whoops, forgot to explain that I added "Floor" column at Column C.

    See attached.
    Attached Files Attached Files
    Last edited by CK76; 02-02-2021 at 02:50 PM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
  3. 02-02-2021, 03:03 PM #3
    daniellegardner1
    daniellegardner1 is offline
    Registered User
    Join Date
    02-02-2021
    Location
    Colorado, USA
    MS-Off Ver
    2019
    Posts
    5

    Re: Excel Formula for Multi Tiered Pricing

    THANK YOU! This seems to be working. Can you possibly explain it in lemans terms? I appreciate your help so much!
    Reply With QuoteRegister To Reply
  4. 02-02-2021, 04:34 PM #4
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Excel Formula for Multi Tiered Pricing

    You are welcome.

    As for the explanation... SUMPRODUCT multiplies array and sums result.

    And when numeric operation is performed on True/False array, it's implicitly interpreted as 1/0.

    So... below is checking that Floor value of each range is less than total sold.
    (F3>C5:C10) = {1, 1, 1, 0, 0, 0}

    Below, checks that entire tier is within range.
    ((C5:C10+5000)<F3) = {1, 1, 0, 0, 0, 0}

    Only 0-5000 & 5001-10000 is charged in it's entirety.

    Since price is per 1000. Price is multiplied by 5 and added together.
    =SUMPRODUCT((F3>C5:C10)*(C5:C10+5000)<F3)*(B5:B10)*5)
    =SUMPRODUCT({1,1,1,0,0,0}*{1,1,0,0,0}*{3,3.45,3.97,4.56,5.25,6.03}*5)
    =SUMPRODUCT({3,3.45,0,0,0,0}*5)
    =3*5 + 3.45*5
    = 32.25

    Next, SUMPRODUCT, is checking for any tier that is partly covered by total used.
    i.e. Where floor is less than 12000 AND floor + 5000 is greater than 12000.
    =SUMPRODUCT((F3>C5:C10)*((C5:C10+5000)>F3)*(B5:B10)*((F3-C5:C10)/1000))
    =SUMPRODUCT({1,1,1,0,0,0}*{0,0,1,0,0,0}*{3,3.45,3.97,4.56,5.25,6.03}*(12000-{0,5001,10001,15001,20001,25001})/1000)
    =SUMPRODUCT({0,0,3.97,0,0,0}*{12,7,2,-3,-8,-13})
    =3.97*2

    Then add base charge, so...
    32.25 + 7.94 + 21.53 = 61.72

    Hope this is simple enough explanation.
    Reply With QuoteRegister To Reply
  5. 02-02-2021, 04:34 PM #5
    daniellegardner1
    daniellegardner1 is offline
    Registered User
    Join Date
    02-02-2021
    Location
    Colorado, USA
    MS-Off Ver
    2019
    Posts
    5

    Re: Excel Formula for Multi Tiered Pricing

    Okay so upon further investigation, the proposed formula is not working correctly for ONLY 5000. Example- if someone uses 5000 gallons, the formula is only giving me only the base rate. The other vales seem to be ok. Any ideas?
    Reply With QuoteRegister To Reply
  6. 02-02-2021, 04:37 PM #6
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Excel Formula for Multi Tiered Pricing

    Change <F3 to <=F3.
    Reply With QuoteRegister To Reply
  7. 02-02-2021, 05:36 PM #7
    daniellegardner1
    daniellegardner1 is offline
    Registered User
    Join Date
    02-02-2021
    Location
    Colorado, USA
    MS-Off Ver
    2019
    Posts
    5

    Re: Excel Formula for Multi Tiered Pricing

    Got it, thank you. I tried this earlier and it didn't work for some reason.

    SECOND QUESTION- the formula isn't working over 30,000 gallons used. I think due to our formula adding the 5000, and our last value being 25,000. Is there a way to remove the ceiling there? I've tried adding another sumproduct but can't seem to get it right.
    Reply With QuoteRegister To Reply
  8. 02-02-2021, 06:06 PM #8
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Excel Formula for Multi Tiered Pricing

    Oh, right. I forgot to account for that.

    Since you only need to consider case where F3>=C10. There's no need for additional SUMPRODUCT.

    Ex:
    =SUMPRODUCT((F3>C5:C10)*((C5:C10+5000)<F3)*(B5:B10)*5)+SUMPRODUCT((F3>C5:C10)*((C5:C10+5000)>F3)*(B5:B10)*((F3-C5:C10)/1000))+21.53+((F3>=C10)*(F3-C10-5000)/1000*B10)

    Edit: reversed sign for +5000. Since first 5000 after Floor is accounted for in second SUMPRODUCT.
    Last edited by CK76; 02-02-2021 at 06:09 PM.
    Reply With QuoteRegister To Reply
  9. 02-02-2021, 06:57 PM #9
    daniellegardner1
    daniellegardner1 is offline
    Registered User
    Join Date
    02-02-2021
    Location
    Colorado, USA
    MS-Off Ver
    2019
    Posts
    5

    Re: Excel Formula for Multi Tiered Pricing

    Hmmm- it doesn't seem to be working. The values are incorrect for 25,000 and up are higher than they should be.

    Ex: 25,000 gallons should equal $122.68 but the new formula is giving me $152.83
    Reply With QuoteRegister To Reply
  10. 02-02-2021, 08:23 PM #10
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Excel Formula for Multi Tiered Pricing

    Hmm... let me look at it tomorrow. I should have tested all scenarios.
    Reply With QuoteRegister To Reply
  11. 02-03-2021, 09:47 AM #11
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,913

    Re: Excel Formula for Multi Tiered Pricing

    Ok, tested my formula in Post #8. That does return 122.67.

    To return 122.68, you just have to account for 1 gallon gap between ceiling and floor in your charge rate tiers.
    You defined first tier as 0-5000, and 2nd from 5001-10000. So there is 1 gallon between 5000 to 5001 etc not being accounted for.

    Change marked below.

    =SUMPRODUCT((F3>C5:C10)*((C5:C10+5000)<F3)*(B5:B10)*5)+SUMPRODUCT((F3>C5:C10)*((C5:C10+5000)>F3)*(B5:B10)*((F3+1-C5:C10)/1000))+21.53+((F3>=C10)*(F3-C10-5000)/1000*B10)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula for Tiered pricing with various pricing matrix
    By kunaltalreja in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2020, 11:37 AM
  2. Formula for tiered pricing
    By brems in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2018, 12:02 PM
  3. Tiered Pricing formula
    By ahmedrz81 in forum Excel General
    Replies: 4
    Last Post: 10-25-2018, 12:09 PM
  4. Tiered Pricing Formula Help
    By jbonsignore in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-11-2018, 09:39 AM
  5. [SOLVED] Tiered Pricing formula
    By egotrich in forum Excel General
    Replies: 11
    Last Post: 07-28-2017, 03:19 PM
  6. [SOLVED] Formula for Tiered Pricing
    By estallings85 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-26-2017, 12:16 PM
  7. [SOLVED] Formula for tiered pricing
    By riaface in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2014, 12:38 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1