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

 

[boxsterman]

This sounds like algebra to me.... sounds like you have 3 variables with 2 unknowns.... x, y and z.

I could help if I had more information... plus what is an artikel?

 

[yankee428]

Divide the number of aricles you would like in the box by the number of colors that you would like in the box. For example, if you want 12 articles in the box and want to use 3 colors, then the formula in Excel would be =12/3, which will equal 4 of each color. If there is a remainder, you will need to decide which extra color to use.

 

[bullit_nl]

Excuse me for my bad english. I mean Articles

 

[bullit_nl]

Divide the number of aricles you would like in the box by the number of colors that you would like in the box. For example, if you want 12 articles in the box and want to use 3 colors, then the formula in Excel would be =12/3, which will equal 4 of each color. If there is a remainder, you will need to decide which extra color to use.

OK. Lets say i have 12 articles. From each article i have a few thousand (not all exactly the same). Now i wan to make 35 boxes with 10 articles (doesn't matter witch colors) with a minimal of 5 colors. Can excel calculate for me or advice me how many i have to take from each color (article)?

 

[yankee428]

Yes, Excel can perform the calculations, but you need to build the algorithm (ie logic) behind it. So if you wanted to build 35 boxes with 10 articles per box, min 5 different colors and you have 1000 colors you could build a list of all the colors and associate them with a number (1 to 1000). You could then use a formula like =rand()*1000 in number format rounded to 0 decimal places. copy down 10 cells, then perform a copy/paste special values. From here in the next column you can use a vlookup to lookup what color from your color list is associated with these 10 random pics. Repeat 35 times.

From here you can build your algorithm. In this case we can skip this step because using 10 random numbers to a 3 places in Excel will always give you at least 5 that are unique.

If you need to build some complex rules sets, such as make sure customer X does not receive the more than 6 duplicates in 5 orders, or tie this into an inventory system, then Excel could be very useful to you. If your example is the extent of it, I personally would just run the math =10/5 is 2 and so, grab 2 articles out of 5 random containers and let my brain just pick which colors at random.

 

[bullit_nl]

Building the algorithm sounds complicated. I have no clue on how to do that.

And I forgot the mention the bundle part. Sorry.

Example:

I Have 598390 M&M’s

This are the colors:

Bi-color 67.589
Crème 36.548
Yellow 101.254
Orange 98.546
Purple 80.000
Red 99.654
Pink 66.543
White 48.256
Other 0
Other 0
Other 0

I want to bundle 30 M&M's from the same color
Put 6 bundles in a box
Make 2875 boxes
With a minimal of 5 colors per box

Thanks

 

[GorD]

It sounds a job for solver but I'm afraid I can't follow the information well enough to try to set it up

What are M&M's and what is the 598390- is that the total number of pieces avaiable, also what do the nymbers after each colour represent - are they relevant or colour codes?

 

[bullit_nl]

M&M's are the candies. But it's just a exapmle.

598390 is the total number of pieces available.

The numbers after each colour are the number of pieces available per colour. All this numbers togehter make the 598390.

 

[GorD]

Sorry - how do all the numbers together make 598390 and why are they all they listed to 3 decimal places if there pieces of candy avaiable?