Solver isn't quite enough..

nielsvanautenboer · Aug 27, 2013

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?

nielsvanautenboer · Aug 27, 2013

=IF(D4="";"";IF(E4="";D4/C4;IF(AND(((E4-INT(E4))*(B4)/C4)<Gerechten!E4;((E4-INT(E4))*(B4)/C4)/C4<Gerechten!E4);"";((E4-INT(E4))*B4)/C4)))

One of my formulas.. and still it doesn't work..

nielsvanautenboer · Aug 27, 2013

A	B	C	D	E	F
Big	Small	Needed	Ratio	# Big	# Small
				=IF((C2/A2)/ROUNDUP((C2/A2);0)>D2; ROUNDDOWN(C2/A2;0);ROUNDUP(C2/A2; 0))	=IF((C2-(E2A2))/B2<0;0; ROUNDUP((C2-(E2A2))/B2;0))
10	3	19,3	0,9	1	4

With this formula it still gives the answer to buy 1 big and 4 small containers. Why doesn't he give me 2 big containers?

pgc01 · Aug 27, 2013

Hi
Welcome to the board

This is just a contribution, since I don't think there's enough information in your post.

Your post does not make completely sense to me, because you say

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

but then I don't see you factoring the prices in your algorithm.

The way I'd do it is to use 2 equations

1 equation that calculates the total volume
1 equation that calculates the total cost

then use solver, with the constraint
Total volume >= goal

and calculate the Minimum for the cost.

nielsvanautenboer · Aug 27, 2013

First of all, thanks for your reply!

Lets state that the biggest volume is always the cheapest. (we have a shop where you can buy milk at 10 liters)

There isn't always a perfect solution; let me explain..

If you need 14 liters of milk and the Volumes are: 5 liters and 3 liters, you can get:

< 2 x 5 Liters > and < 1 x 3 Liters > TOTAL: 13 LITERS
or
< 2 x 5 Liters > and < 2 x 3 Liters > TOTAL: 16 LITERS

If my goal is 14 Liters, neither of them are the perfect combination..
But that's why I prefer buying 13 Liters, because it is closer to 14 then 16 is. (Thats why I used the RATIO column.) I tried Solver, and it works, but I don't manage to get it in for 300 rows

I hope this clears out the confusion?

Greetings,
Niels

marious · Aug 27, 2013

nielsvanautenboer said:
First of all, thanks for your reply!

Lets state that the biggest volume is always the cheapest. (we have a shop where you can buy milk at 10 liters)

There isn't always a perfect solution; let me explain..

If you need 14 liters of milk and the Volumes are: 5 liters and 3 liters, you can get:

< 2 x 5 Liters > and < 1 x 3 Liters > TOTAL: 13 LITERS
or
< 2 x 5 Liters > and < 2 x 3 Liters > TOTAL: 16 LITERS

If my goal is 14 Liters, neither of them are the perfect combination..
But that's why I prefer buying 13 Liters, because it is closer to 14 then 16 is. (Thats why I used the RATIO column.) I tried Solver, and it works, but I don't manage to get it in for 300 rows

I hope this clears out the confusion?

Greetings,
Niels

Test this with Solver
Set Target $I$3
Equal: Choose min option
By changing Cells $H$2:$H$3
Constraints
$H$2>=0
$H$3>=0
$I$2=$A$2
$J$2=0
<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

D

E

F

G

H

I

J

1

Buying constraints in liters

Size in liters

Pack

Price per pack

Unit price

Price per liter

liter per Pack

Packs to Buy

solver

Partial

2

37.00

1

3

28

2.333

3

5

2

89

3.000

milk

?
?

?
</body></html>

nielsvanautenboer · Aug 27, 2013

Wow, how do you post this so nicely?

I will explain:

So I am a "leader" of the Scouts Group in Belgium,
We go to camp each year. We have to buy food and drinks for over 270 people every year. We divide them in 7 groups (keep in mind that that's why I need the different volumes).

So suppose Group 1, called the "Wolves" need 7 liters of Milk. I have to buy 7 liters of Milk for them.
The price of the pack of 5 liters is 2 euros
The price of the pack of 2 liters is 1 euro

I need 7 liters so I buy 1 pack of 5 and 1 pack of 2 Liters, right? Together that 7 liters..
Anyway, the price isn't important but you will see..

I need excel to figure out;

I need 7 liters,
I buy 1 x 5 liters,
I buy 1 x 2 liters.

That's all I need.. To divide the 7 liters in the amount of the pack.
It seems really really simple, but my sister-in-law has a double diploma in statistics, and still she doesn't find it ( because she is not PERFECT with excel, (she's good))

Thanks,

Niels

shg · Aug 27, 2013

Code:

       -A- ---B---- C ---D---- ---------------E---------------
   1                   Needed                                 
   2                       16  D2: Input                      
   3                   Bought                                 
   4                       17  D4: =SUMPRODUCT(A8:A10, D8:D10)
   5                    Cost                                  
   6                  $ 18.75  D6: =SUMPRODUCT(B8:B10, D8:D10)
   7   Pkg Cost/Pkg     Qty                                   
   8   10  $ 10.00          1                                 
   9    7   $ 8.75          1                                 
  10    2   $ 3.00          0

Solver:

Set D6 to Min

By changing D8:D10

Constrain D8:D10 >= 0

Constrain D8:D10 Int

Constrain D4 >= D2

marious · Aug 27, 2013

nielsvanautenboer said:
Wow, how do you post this so nicely?

I will explain:

So I am a "leader" of the Scouts Group in Belgium,
We go to camp each year. We have to buy food and drinks for over 270 people every year. We divide them in 7 groups (keep in mind that that's why I need the different volumes).

So suppose Group 1, called the "Wolves" need 7 liters of Milk. I have to buy 7 liters of Milk for them.
The price of the pack of 5 liters is 2 euros
The price of the pack of 2 liters is 1 euro

I need 7 liters so I buy 1 pack of 5 and 1 pack of 2 Liters, right? Together that 7 liters..
Anyway, the price isn't important but you will see..

I need excel to figure out;

I need 7 liters,
I buy 1 x 5 liters,
I buy 1 x 2 liters.

That's all I need.. To divide the 7 liters in the amount of the pack.
It seems really really simple, but my sister-in-law has a double diploma in statistics, and still she doesn't find it ( because she is not PERFECT with excel, (she's good))

Thanks,

Niels

In orange are the data input that you request. I run the same Solver that I posted before and I arrive to the same solution that you arrived.
I use Excel Jeani to post. It is an excel add-in and there are some links in this forum so you can download to excel and post.

www.haserodt.de
<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

D

E

F

G

H

I

J

1

Buying constraints in liters

Size in liters

Units in Pack

Price per pack

Unit price

Price per liter

liter per Pack

Packs to Buy

solver

Partial

2

7.00

1

2

1

1.000

3

5

1

2

1.000

milk

?
?

</body></html>

I will try to create a formula tomorrow if I have time.

Regards,

Mario

pgc01 · Aug 28, 2013

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:

nielsvanautenboer said:
i prefer buying as much of the bigger packs as possible. (They are cheaper)

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

Solver isn't quite enough..

nielsvanautenboer

New Member

Excel Facts

nielsvanautenboer

New Member

nielsvanautenboer

New Member

pgc01

MrExcel MVP

nielsvanautenboer

New Member

marious

Board Regular

nielsvanautenboer

New Member

shg

MrExcel MVP

marious

Board Regular

pgc01

MrExcel MVP

Similar threads

Forum statistics

Share this page

Solver isn't quite enough..

New Member

Excel Facts

New Member

New Member

MrExcel MVP

New Member

Board Regular

New Member

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock