Allocation across Cells

[jlkirk]

In Column A1:A25 I have a list of varying quantities of unique products ranging from 1 to 250 units. In Column B1:B25 I have listed the corresponding costs of each product, which may be duplicated. I receive various orders for mixes of my products. What I would like to do is allocate each order across my list of products to minimize my costs per order.

For example, if I get an order for 25 units of mixed product, and I have 10 units of Product No 1 at $1.50, 20 units of No 2 at $1,75, and 40 units of No 3 at $5.00, the order mix would be made up of 10 units of No 1,and 15 units of No 2. Alternatively, if the order was 35 units, it would be made up of all of No 1, all of No 2 and 5 of No 3.

Any ideas?

Thanks in advance.

 

[KRice]

If you are willing to sort your product table (use Sort & Filter on the Data tab, then choose Filter, and select the drop down in the column heading for UnitPrice and sort smallest to largest), then add an Order Allocation column and paste the formula shown and copy down.

mrexcel_20200408.xlsx
	A	B	C	D	E	F	G
1	Qty	UnitPrice	Product Name	Order Allocation		300	<--Order size
2	38	0.46	Product 2	38
3	58	0.46	Product 4	58
4	29	0.68	Product 7	29
5	134	1.72	Product 8	134
6	27	2.92	Product 23	27
7	25	3.2	Product 24	14
8	148	3.37	Product 20
9	173	3.81	Product 3
10	71	4.38	Product 6
11	27	4.54	Product 13
12	134	4.55	Product 21
13	182	4.86	Product 22
14	6	5.3	Product 14
15	11	5.41	Product 18
16	47	5.61	Product 1
17	191	6.26	Product 19
18	44	6.64	Product 10
19	86	6.7	Product 9
20	141	6.76	Product 25
21	72	7.98	Product 12
22	128	8.97	Product 5
23	62	9.12	Product 17
24	56	9.19	Product 16
25	4	9.21	Product 11
26	134	9.23	Product 15
Sheet9

Cell Formulas
Range	Formula
D2:D26	D2	=IF(SUM(D$1:D1)<$F$1,MIN(A2,$F$1-SUM(D$1:D1)),"")