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
[TABLE="width: 195"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 2"]

Change Maker[/TD]
[/TR]
</tbody>[/TABLE]

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



[TABLE="width: 195"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]

[/TD]
[TD]A B [/TD]
[TD="align: right"]C
1234.56[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]34.56[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]34.56[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]14.56[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]4.56[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]4.56[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]0.56[/TD]
[/TR]
[TR]
[TD="align: right"]0.25[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]0.06[/TD]
[/TR]
[TR]
[TD="align: right"]0.1[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]0.06[/TD]
[/TR]
[TR]
[TD="align: right"]0.05[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]0.01[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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
Formula	Available

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]932.05[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]32.05[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]32.05[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12.05[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.05[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.05[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.05[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.05[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.05[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

</tbody>

​

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

	A	B	C	D
Formula	Available

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]932.05[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]432.05[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]182.05[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]82.05[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]32.05[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7.05[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2.05[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.05[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

</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!!!