Let Excel Calculate

bullit_nl

Active Member
Joined
Jun 27, 2002
Messages
273
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

boxsterman

Active Member
Joined
Apr 16, 2002
Messages
279
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

Active Member
Joined
Apr 12, 2004
Messages
348
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
Joined
Jun 27, 2002
Messages
273

ADVERTISEMENT

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
Joined
Apr 12, 2004
Messages
348
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
Joined
Jun 27, 2002
Messages
273

ADVERTISEMENT

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
Joined
Jan 17, 2004
Messages
1,446
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
Joined
Jun 27, 2002
Messages
273
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
Joined
Jan 17, 2004
Messages
1,446
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?
 

Forum statistics

Threads
1,147,844
Messages
5,743,512
Members
423,800
Latest member
IuneKeiki

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top