# Calculating currency amounts (notes/bills and coins)

#### HolmesLane

##### New Member
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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

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)

Replies
3
Views
156
Replies
4
Views
447
Replies
1
Views
346
Replies
2
Views
323
Replies
1
Views
539

1,221,121
Messages
6,158,060
Members
451,460
Latest member
DanielleS

### 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.

### Which adblocker are you using?

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

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