# Let Excel Calculate

#### bullit_nl

##### Active Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### boxsterman

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

#### yankee428

##### Active Member
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

##### Active Member
Excuse me for my bad english. I mean Articles

#### bullit_nl

##### Active Member
yankee428 said:
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

##### Active Member
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

##### Active Member
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

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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?

Replies
1
Views
399
Replies
11
Views
392
Replies
4
Views
162
Replies
0
Views
222
Replies
5
Views
369

1,181,814
Messages
5,932,204
Members
436,823
Latest member
kailose

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back