Let Excel Calculate

[bullit_nl]

I wanna know if it's possible to let excel calculate for me how many artikels from each color i have to take to fill a box with a certain number of artikels. For example:

I know how many artikels i have by color
I know how many artikels i want to put in the box (this is not always the same)
I know how many colors i want to have in the box (this is not always the same)

With this facts i want excel to calculate for me.

Thanks

 

[GorD]

Okay so its not 67.589 its 67589 etc

 

[bullit_nl]

How do all the numbers together make 598390??

I used 1000 Separator (.) that's why. The 3 decimal means nothing.

 

[GorD]

Okay, I can't get this to work as well as I would have liked as It would be better to find the maximum number of boxes that can be made - for some reason it hangs solver but it works okay when trying to find your exact value.

Part 1 as too big for single post

combinations permutations macro.xls
	K	L	M	N	O	P	Q	R	S	T
1	Stock	67589	36548	101254	98546	80000	99654	66543	48256
2	Qtyused	61560	29130	85620	85740	72300	81990	58860	42300
3
4		Bi-color	Crme	Yellow	Orange	Purple	Red	pink	white	Noofboxes
5	box1	30	30	30	30	30	30			2
6	box2	30	30	30	30	30		30		2
7	box3	30	30	30	30	30			30	2
8	box4	30	30	30	30		30	30		287
9	box5	30	30	30	30		30		30	2
10	box6	30	30	30	30			30	30	2
11	box7	30	30	30		30	30	30		2
12	box8	30	30	30		30	30		30	2
13	box9	30	30	30		30		30	30	2
14	box10	30	30	30			30	30	30	2
15	box11	30	30		30	30	30	30		3
16	box12	30	30		30	30	30		30	3
17	box13	30	30		30	30		30	30	3
18	box14	30	30		30		30	30	30	3
19	box15	30	30			30	30	30	30	3
Sheet2

 

[GorD]

combinations permutations macro.xls
	K	L	M	N	O	P	Q	R	S	T
20	box16	30		30	30	30	30	30		574
21	box17	30		30	30	30	30		30	879
22	box18	30		30	30	30		30	30	128
23	box19	30		30	30		30	30	30	145
24	box20	30		30		30	30	30	30	3
25	box21	30			30	30	30	30	30	3
26	box22		30	30	30	30	30	30		595
27	box23		30	30	30	30	30		30	23
28	box24		30	30	30	30		30	30	3
29	box25		30	30	30		30	30	30	24
30	box26		30	30		30	30	30	30	3
31	box27		30		30	30	30	30	30	3
32	box28			30	30	30	30	30	30	172
33
34								totalboxes		2875
Sheet2

Solver ( which is an addin - tools addins)

set target to exact value and enter the value
by changing cells t5:t32
constraints are L2:s2 are =< l1:s1 ( qty used =< qty available)
t5:t32 are >=0
and t5:t32 are integers

Anyone know why it has problems when set to maximum - too many variables?

 

[GorD]

Oh, I forgot to say that this set up is for all the options of combining 6 colours from 8. So each bag would have 6 different colours rather than 5 different plus any one other. I think that would have too many permutations

 

[bullit_nl]

Thanks GorD.

But i'm looking for a different outcome. I would like to know from excel how many boxes i can make with the same mix and when that mix is finisched the next mix i have to make.