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?

what price will be for 400 units?

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.

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.

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.

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.