+ Reply to Thread
Results 1 to 13 of 13

Looking for Progressive Pricing Formula w/Bulk Sales

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    23

    Looking for Progressive Pricing Formula w/Bulk Sales

    I would like a formula, similar to progressive pricing, but where the "packs" can't be broken into units. So, for any given order quantity, the biggest packs (which have the best rate) would be selected, then the remaining balance would pick the next appropriate size pack to fulfill the order. I'll provide my example on this.

    Qty Price
    5 $0.50
    25 $2.25
    125 $11.00
    250 $19.00
    550 $36.00

    Rules:
    1. Packs can't be broken up
    2. Quantity must equal or exceed to fulfill an order
    3. In a case where multiple combos have equivalent price, the tiebreaker is the least number of packs. (This is not a critical rule and can be ignored if it adds too much complexity)

    Examples:

    Order Packs Cost
    114 4x25+3x5 $10.50
    118 1x125 (or same cost 4x25+4x5) $11.00
    121 1x125 $11.00
    570 1x550+4x5 (this is cheaper than adding 1x25) $38.00

    Is there a formula that can calculate the order cost, based on a given quantity?
    Last edited by hawaean; 10-22-2019 at 07:27 AM. Reason: Clarification on the examples.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,190

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    What would the expected outcome for 249 be? Talk us through it, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    23

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    Hi Ali. Thanks for helping.

    249 would be the 250 pack, because it's the cheapest to fulfill the order. The final tally can't be short.

    If you try any other combo, the cost is higher than $19. The 2x125 packs would cost $22. The 1x125 + 5x25 packs cost $22.25.

    245 is an interesting order, because either the 250 pack or the next combo (1x125, 4x25, 4x5) are both $19. Shipping (which I don't need calculated here) would be cheaper for the 250 pack, so it would be nice if there's a way to have the larger packs break the tie.

    I figure the formula needs to identify the biggest pack and quantity, adding smaller packs as needed. If more than one fulfills the order, then the combo with the bigger packs win.

    Does that help?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,190

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    Yes, it helps. I am afraid I don't have a solution for you - too tricky for me, sorry. Someone else will know how to do it.

  5. #5
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,269

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    what price will be for 400 units?
    In order to ask a question you must already know most of the answer. Ask A Foolish Question, Robert Sheckley

  6. #6
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    23

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    Hi Tim, Sorry I didn't see your question earlier.

    400 units would be:

    250 x 1 (19.00)
    125 x 1 (11.00)
    25 x 1 (2.25)
    = $32.35

    The 550 units is still too expensive at $36 and there's no other combo that comes out cheaper.

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    I've looked at this for hour or two (more!!) or so over the last few days..... I can understand what you want OK.... but it is NOT easy to deliver!! The 5 pack as the lowest packsize and the requirement for the least-cost formulation turns a simple problem into a nightmare!!!
    Glenn



  8. #8
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    23

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    Hi Glenn, I thought that my limited excel knowledge was the bottleneck, but it's a little comforting (silver lining?) to know that even the experts are getting stumped on this one.

    I actually had high hopes for this one because conceptually, it seems pretty straight forward and easy to understand. I've posted and read about much more complicated scenarios where members here have knocked them out of the park as if they were simple IF functions.

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    Maybe I'm looking at it the wrong way round. It happens all too often!!!

  10. #10
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    23

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    I took a stab at it, but it was nesting a lot of ROUNDDOWN formulas in IF/SUMPRODUCT statements. It kind of worked, because if the order number divided less than 1, then the rounding function would return a 0. But like you said, going 5 layers in would make a lengthy formula.

    I'm still a little hopeful someone will have a creative solution to this. I have a hunch that this might require pre-calculating some of the steps in another column, but I'm fine with that. Even if it's a bit of up-front time and effort, this will make my life a lot easier for the long run.

    Thanks all.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    11,076

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    Same here. Though I've been nesting QUOTIENT/MOD/CEILING formulas. Thought I had it 'till you "dumped" 245 on us ( LOL).
    Dave

  12. #12
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    23
    Quote Originally Posted by FlameRetired View Post
    Same here. Though I've been nesting QUOTIENT/MOD/CEILING formulas. Thought I had it 'till you "dumped" 245 on us ( LOL).
    Out of curiosity, what did you come up with? The ties are not as critical to factor in.

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    11,076

    Re: Looking for Progressive Pricing Formula w/Bulk Sales

    In the attached ignore the duplicated sheet. Working out another way there.

    Also I broke it out of the nested formulas.
    In columns I and K to return packs
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In columns J and L to return Quantities
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then the final cost in column M
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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