Denominations Question

[Guest]

Hi,

I have a sheet with amounts to be paid to employees. The employees need to
be paid in cash every week. These amounts normally don't exceed $1000.
However, it is a nightmare if the exact denominations are not withdrawn from
the bank. Is there some way I can determine the number of currency notes to
be withdrawn in denominations of 1000, 500, 200, 100, 50, 20, 10, 5 & 1's?
Can I do this in excel?

Thanks and regards,

neil

 

[Nikos Yannacopoulos]

Neil,

Assuming the amounts to be paid are in column D starting D2 down:
Populate cells E1 through M1 with the denomitations 1000, 500, ...., 1
Put the following formula in cell E2:
=INT(D2/E$1)
Then put the following formula in cell F2:
=INT(($D2-SUMPRODUCT($E$1:E$1,$E2:E2))/F$1)
and copy accross up to cell M2
Then copy range E2:M2 all the way down to the end of the payments list. Now
in each row you have the number of denominations for that payment. Just sum
the denominations at the bottom, and you get what you need.

Adjust the formulae for different payment cells as required.

HTH,
Nikos

 

[Dave Peterson]

Chip Pearson posted a neat function awhile ago:

http://google.com/groups?threadm=#[email protected]
(one line in your browser)

This is Chip's modifed function:

Option Explicit

Function ConvertToCurrency(ByVal Amt As Double) As Variant
Dim Ndx As Integer
Dim Counter As Integer
Dim Arr As Variant
Arr = Array(1000, 500, 200, 100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.0001) >= Arr(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function

This actually goes down to the penny.

Put
1000, 500, 200, 100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01
in B1:N1 (for headers)

Put your value in A2.
Select B2:N2 and type this:
=converttocurrency(a2)
but hit ctrl-shift-enter instead of just enter.

And stand back!

If you want to drop the change, just ammend this line:
Arr = Array(1000, 500, 200, 100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
to
Arr = Array(1000, 500, 200, 100, 50, 20, 10, 5, 1)

And select B2:J2 before you hit shift-ctrl-enter.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out.