Calculating currency amounts (notes/bills and coins)

[HolmesLane]

I work for a charity who pays some of its staff and volunteers in cash. Each one is a different amount and we need to work out for each person how many of each denomination of notes/bills and coins we need. Then add all those up to get the money from the bank!

I'm convinced a spreadsheet should be able to do it but how?

 

[jolivanes]

I remember that Wigi had one on this or one of the other forums. His was in Euros but probably could be adapted to other currencies. I don't have the time to look for it now but in the meantime you could look him up.
Maybe Erik van Geit remembers?
Regards.
John

 

[barry houdini]

Which currency?

Here's a version for UK with £ denominations up to 50, obviously you can alter the denominations as you see fit - and include pence/cents if you wish

Book1
	A	B	C	D	E	F	G	H
1		50	20	10	5	2	1
2	Amount
3	167.00	3	0	1	1	1	0
4	265.00	5	0	1	1	0	0
5	401.00	8	0	0	0	0	1
6	223.00	4	1	0	0	1	1
7
8	Total	20	1	2	2	2	2
9
Sheet1

formula in B3 copied down

=INT(A3/B$1)

Formula in C3 copied across and down

=INT(($A3-SUMPRODUCT($B3:B3,$B$1:B$1))/C$1)

edit: note when I tested a version with coins and notes I got some rounding errors so i amended C3 formula to

=INT((ROUND($A3-SUMPRODUCT($B3:L3,$B$1:L$1),2))/M$1)

 

[jolivanes]

Here is another way. You might be able to adapt.

http://www.ozgrid.com/forum/showthread.php?t=50400&highlight=euro

John