# Looking for Progressive Pricing Formula w/Bulk Sales

1. ## 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?

2. ## Re: Looking for Progressive Pricing Formula w/Bulk Sales

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

3. ## 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. ## 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. ## Re: Looking for Progressive Pricing Formula w/Bulk Sales

what price will be for 400 units?

6. ## 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. ## 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!!!

8. ## 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. ## 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. ## 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. ## 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).

12. Originally Posted by FlameRetired
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. ## 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:
`Please Login or Register  to view this content.`
and
Formula:
`Please Login or Register  to view this content.`

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

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

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