Solver isn't quite enough..

[nielsvanautenboer]

Hello members of this great forum.

First time post but longtime viewer

As you could get; I have a problem..

I have 300 lines of products, i'll take MILK as an example.

I need 13,3 liters of milk, and they sell them in 2 quantities; 5 liters and 1 liters.

As you could guess i should buy < 2 x 5 liters > and < 3 x 1 liters >.

As there sometimes isn't a perfect solution, i prefer buying as much of the bigger packs as possible. (They are cheaper)

I CAN calculate it using Solver or Goal Seek but it's never quit perfect. I opted using a RATIO line, where I state that if excel chooses a package, it doesn't need to be perfect. A ratio like 0,9 means that I am willing to throw away 0,1 liter.

Anyone an idea of a Formula?

 

[marious]

Hello members of this great forum.

First time post but longtime viewer

As you could get; I have a problem..

I have 300 lines of products, i'll take MILK as an example.

I need 13,3 liters of milk, and they sell them in 2 quantities; 5 liters and 1 liters.

As you could guess i should buy < 2 x 5 liters > and < 3 x 1 liters >.

As there sometimes isn't a perfect solution, i prefer buying as much of the bigger packs as possible. (They are cheaper)

I CAN calculate it using Solver or Goal Seek but it's never quit perfect. I opted using a RATIO line, where I state that if excel chooses a package, it doesn't need to be perfect. A ratio like 0,9 means that I am willing to throw away 0,1 liter.

Anyone an idea of a Formula?

Input your data in orange cells. Solution will appear in gray cells.
You stated that you have 300 products, so you probably need to modify the formula so it works whenever you populated down.</SPAN>
I have a question. Someone post a solution using solver that sets a constraint in Solver as INT. Thank you for the advice. I used the MOD, but how do you do it? Just type INT?</SPAN>
Hope it helps. </SPAN>
Regards, </SPAN>
Mario us.</SPAN>

<!-- ######### Start Created Html Code To Copy ########## -->
milk

*

A

B

C

D

E

F

G

H

I

J

K

L

11

Buying *constraints in liters

Size in liters

Units in Pack

Price per Pack

Unit price

Price per liter

liter per Pack

What to buy

Quantity to buy

Cheap

MOD Remaining in liters

Expenisve

12

26.00

1

2

1

0.5

0.5

2

0.4

5.2

5 Packs at 0.4 euros per liter

1

2 expensive pack and 0 Remainder

13

*

5

1

2

2

0.4

5

*

*

*

*

*

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 134px"><COL style="WIDTH: 78px"><COL style="WIDTH: 82px"><COL style="WIDTH: 70px"><COL style="WIDTH: 74px"><COL style="WIDTH: 97px"><COL style="WIDTH: 84px"><COL style="WIDTH: 95px"><COL style="WIDTH: 125px"><COL style="WIDTH: 186px"><COL style="WIDTH: 124px"><COL style="WIDTH: 85px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula E12 =D12/C12 F12 =E12/B12 G12 =C12*B12 H12 =MIN(F12:F13) I12 =$A$12/INDEX($B$12:$F$13,MATCH(H12,$F$12:$F$13,0),1)*INDEX($B$12:$F$13,MATCH(H12,$F$12:$F$13,0),2) J12 =INT($A$12/INDEX($B$12:$F$13,MATCH(H12,$F$12:$F$13,0),1)*INDEX($B$12:$F$13,MATCH(H12,$F$12:$F$13,0),2))&" Packs at "&H12&" euros per liter" K12 =MOD(I12,1)*INDEX($B$12:$F$13,MATCH(MIN($F$12:$F$13),$F$12:$F$13,0),1)*INDEX($B$12:$F$13,MATCH(MIN($F$12:$F$13),$F$12:$F$13,0),2) L12 =IFERROR(INT(INDEX($B$12:$F$13,MATCH(MAX(F12:F13),$F$12:$F$13,0),1)*INDEX($B$12:$F$13,MATCH(MAX(F12:F13),$F$12:$F$13,0),2)/(MOD(I12,1)*INDEX($B$12:$F$13,MATCH(MIN($F$12:$F$13),$F$12:$F$13,0),1)*INDEX($B$12:$F$13,MATCH(MIN($F$12:$F$13),$F$12:$F$13,0),2)))&" expensive pack and "&MOD(INT(INDEX($B$12:$F$13,MATCH(MAX(F12:F13),$F$12:$F$13,0),1)*INDEX($B$12:$F$13,MATCH(MAX(F12:F13),$F$12:$F$13,0),2)/(MOD(I12,1)*INDEX($B$12:$F$13,MATCH(MIN($F$12:$F$13),$F$12:$F$13,0),1)*INDEX($B$12:$F$13,MATCH(MIN($F$12:$F$13),$F$12:$F$13,0),2))),1)&" Remainder", " remainder not big enough to fill an order of the expensive pack") E13 =D13/C13 F13 =E13/B13 G13 =C13*B13 <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4
　
　
<!-- ######### End Created Html Code To Copy ########## -->

 

[nielsvanautenboer]

Really thanks for all of your efforts!!! :D

Indeed, I want the second solution. I want 1 5 liter pack..

but I don't have the pricing for the different quantities.. If I want that I have to go to 3 shops and write up pricing for 300 products

So I just prefer big packs always.. (It's my logic, the bigger, the cheaper? )

Cost is important, but I can't have all the prices.. :D

Greetings,
Niels

Thanks!!!!!

Hi Niels

I see that this has advanced a lot, so maybe you already got a solution.

I will, anyway, try to explain why I said that this approach was not clear.

I'll use this example:

The price of the pack of 5 liters is 3 euros
The price of the pack of 1 liter is 1 euro

My goal is to get 4 liters

I will consider 2 solutions:

Solution 1 - 4 packs of 1 liter
Solution 2 - 1 pack of 5 liters

In terms of volume

Solution 1 is exactly what you want, 4 liters
Solution 2 is not exactly what you want, it's 1 liter more than the goal

In terms of cost

Solution 1 costs 4 euros
Solution 2 costs 3 euros

So, although solution 2 is not as good in terms of getting the exact volume, it's less expensive. Usually this means that that's the one you choose, the less expensive, even if the volume is not exactly what you need.



This is why I said that I was confused because you did not factor the costs in your solution.

And you posted that cost is important:



Maybe you know that this type of case will never happen and that's why you don't want to consider it?

 

[nielsvanautenboer]

Ok I am going to give you guys the whole problem..

We go to camp with over 270 people every year. We have 7 groups of youngsters :D
We have 5 "camps" where the food has to be divided..
One girl is in charge of getting the food lists ready for everyone. She (we) doesn't get payed for this, its voluntarily :D
She spends about 4 days manually entering every day's food list into an excel file and after all this effort is isn't even good.

So I volunteered to help her, by making a fully automated excel-file!

It starts with a schedule, every group sometimes eats a different meal every day. So we have about 50 meals that have to be entered.
Afterwords, that schedule is transfered into lists, lists that have several purposes. We have to have a lot of lists:

1) A list for the products we can buy in advance
2) A list for the products we buy fresh every day
3) A list for the people who have to divide products for each group
4) A list for the fresh products we have to divide

So I am going to have an excel file with over 30 tabs because I need 4 tabs for every group.

A guy of the age of 17 needs more food than one of 8.

We are (for a youth group) pretty rich don't understand me wrong, everyone is a volunteer.
So cost isn't 300% important. If the milk is 1 euro more expensive in one way, it doesn't matter much.
The thing we want to save is the work of our volunteers. These people do so much work and they deserve a good list to make sure everything goes smoothly

If you want i can give you the excel file, but it's way too big and way too difficultly made