mix and match the best composition

[datiduke]

Hi all,
I need to have one or two formulas, which gives me the cheapest composition of multiple sizes.

Lets say for a party I need a minimum of 18l of beer. The beer kegs are available only in 1l for $3, 2.5l for $7, 5l for $13 , 20l for $50 sizes and prices.

Now I need to calculate, what is the cheapest composition of kegs:
1x 20l = $50
or 3x5l + 2.5l + 1l = $49 (which would achieve my minimum target of 18l at the lowest cost)

Once I change my required beer to 22l, it would return $56 (20l + 2x1l).

Note: I do not necessarily need to know what keg size and qty of each I need to buy. Just the final $ I have to invest to achieve my target.)

I know I could do it with a list of increment of .5 l and calculate it, but hopefully there is a better formula solution.

PS: Yes, I know the more beer, the better, but that's the task I was given to solve

 

[DILIPandey]

Hi datiduke,

Can you share some sample data ?
You can simply copy paste data here directly from your sheet.
Make sure you have columns auto-aligned before copy paste.


Regards,
DILIPandey

 

[datiduke]

Good morning DiliPandey and thanks for taking on the task.

This is my product, size and price list
Product Beer
20l $50.00
5l $13.00
2.5l $7.00
1l $3.00

and in a random field I have the total litres needed and below there should be the lowest cost to that. (again, I don't mind if we need to break it down into multiple formulas.
Beer needed: 18
Cost: $ ???


Cheers,
Andy