Calculating currency amounts (notes/bills and coins)

HolmesLane

New Member
Joined
Sep 24, 2006
Messages
1
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?
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,612
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
ABCDEFGH
1502010521
2Amount
3167.00301110
4265.00501100
5401.00800001
6223.00410011
7
8Total2012222
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,310
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top