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?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
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)
 

Forum statistics

Threads
1,141,220
Messages
5,705,091
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top