Distribute Bills based on available Denomination

[earthworm]

I have available denominations

Denomination	No of Bills	Amount
5000	2	10000
1000	23	23000
500	28	14000
100	8	800
50	4	200
20	0	0
10	15	150
5	0	0
2	0	0
1	0	0

Based on above what I want is for example if I enter value of 12000 , I want the excel to distribute the bills based on above mentioned Denomination and also minimize max inventory use

I dont want to consume all the Denomination but also keep some for future use , so utilize those that are available in bulk and minimize usage of Denomination that are available in less

quantity. I need an denomination combination answer in separate column allowing the user to easily pick from till.

 

[Dave Patton]

try the following

T10_1807a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1													Total
2	Amount	0	5000	1000	500	100	50	20	10	5	2	1
3	12,000	0	2	2	0	0	0	0	0	0	0	0	12,000
4	1,152	0	0	1	0	1	1	0	0	0	1	0	1,152
5	6	0	0	0	0	0	0	0	0	1	0	1	6
6	777	0	0	0	1	2	1	1	0	1	1	0	777
7	625	0	0	0	1	1	0	1	0	1	0	0	625
8	4,827	0	0	4	1	3	0	1	0	1	1	0	4,827
3bb_

Cell Formulas
Range	Formula
C3:L8	C3	=INT(ROUND($A3-SUMPRODUCT($B3:B3,$B$2:B$2),2)/C$2)
M3:M8	M3	=SUMPRODUCT($C$2:$L$2,C3:L3)