# Is this possible..?

#### andyjlow

##### New Member
Hello,

I have a requirement to be able to allocate certain quantities of product to certain batches of product in the most efficient allocations possible.... for example... I have four quantities of 25 pieces, allocated to a batch quantity of 100.. simple... However....

In my case we have hundreds, sometimes thousands of individual quantities, allocated against possibly three or four batches... i need to identify a formula or a macro that can evaluate that quantity data and organise it into the most efficient allocations under each batch... and feed that data back to me informing me which qty's are allocated to which batch...

Secondly we have a contingency value applied to the total quantities, which is reflected in the individual batch quantities, for example if this contingency is 10% of the sum of all the individual quantities then value of the 10% (in quantity) is spread across the number of batches, if we have four batches they each take 25% of the total contingency value. I therefore need excel allocate the individual quantities to the batches in the most efficient arrangement, but leave the contingency as close to the 10% as possible in each batch in this scenario.

This seems immensely complicated to me and way beyond my capabilities in excel, i am not even sure it is possible..? I very much hope someone can offer some guidance or a potential solution.

I have an excel sheet which displays what i want to achieve, but i need it to be automated as making these calculations with thousands of individual quantities is impossible. I can send through as an example if necessary.

I would appreciate anyone's consideration on this problem and i hope someone can provide some assistance.

Best regards,

Andy

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi,

You need to use the Excel Add-In called Solver ...
Learn how to use it with some basic examples ...
Then, move on to define your problem and constraints ...

HTH

Replies
1
Views
149
Replies
2
Views
313
Replies
6
Views
129
Replies
2
Views
157
Replies
2
Views
203

1,202,911
Messages
6,052,517
Members
444,588
Latest member
ViJN

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