Making Change with Limited and Changing Funds - Dynamic Change Maker

[OrbitalD]

I'm not sure what to call this really but we are trying to find the best way to make change with limited numbers of bills / coins.

I've created a change maker using the MROUND and FLOOR as found in this example and it works well to make change but what happens if we run out of $100 bills or nickels?

https://appscout.pcmag.com/your-questions/274694-teach-excel-to-make-change

Change Maker

<colgroup><col span="3"></colgroup><tbody>
</tbody>

B2: =FLOOR(C1/A2,1)
C2: =MROUND(C1-(B2*A2),0.01)

	A B	C 1234.56
100	12	34.56
50	0	34.56
20	1	14.56
10	1	4.56
5	0	4.56
1	4	0.56
0.25	2	0.06
0.1	0	0.06
0.05	1	0.01
0.01	1	0

<colgroup><col span="3"></colgroup><tbody>
</tbody>

If we only have ten $100 I can manually type 10 into the B2 cell and everything adjusts but we would like to change our mind and enter various numbers of bills / coins while keeping the function of the formulas running. A dynamic change maker with varying numbers of bills / coins.

Does that make sense? Is it possible?

 

[StephenCrump]

Welcome to the Forum!

Perhaps:

B2: =FLOOR(D1/A2,1)
D2: =ROUND(D1-(MIN(B2:C2)*A2),2)

	A	B	C	D
1		Formula	Available	932.05
2	100	9		32.05
3	50	0		32.05
4	20	1		12.05
5	10	1		2.05
6	5	0		2.05
7	1	2		0.05
8	0.25	0		0.05
9	0.1	0		0.05
10	0.05	1		0
11	0.01	0		0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

where column C is left blank. Or, for example:

	A	B	C	D
1		Formula	Available	932.05
2	100	9	5	432.05
3	50	8	5	182.05
4	20	9	5	82.05
5	10	8	5	32.05
6	5	6	5	7.05
7	1	7	5	2.05
8	0.25	8	5	0.8
9	0.1	8	5	0.3
10	0.05	6	5	0.05
11	0.01	5	5	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

 

[OrbitalD]

OHG that's super excellent! Thanks a bunch!

Is there anyway to put the 5 in C2 into B2 once its been entered as available? That way the user just uses all the numbers in column B for making change.

 

[OrbitalD]

Is there anyway to put the 5 in C2 into B2 once its been entered as available? That way the user just uses all the numbers in column B for making change.

OK that was a stupid question!

Thanks again for your help!!!