Here is the situation, you are working somewhere and handling cash every second. You have to manually determine how much number of bills of each denomination you need to payout certain amount of cash. Instead of damaging your brain cells doing repetitive task, why not get Excel help to confirm and increase accuracy? Following is the solution:

This is exactly what we are learning today!

**Building currency bill calculator – Step by step**

Step 1: First determine the currency for which you want to make a bill calculator. Mine is Pakistani rupee. Pakistani currency have following denominations:

- 5000
- 1000
- 500
- 100
- 50
- 20
- 10
- 5
- 2
- 1

I listed them in a column. Give this column a particular heading. Give heading to two next columns as “# of bills” and “Amount” or whatever you desire. This is what I had once done with this step:

Step 2: I have mentioned in cell E6. I named this cell as “amount” by typing in name box directly and hitting enter. It will make writing formula easy for me.

Step 3: In first cell of “# of bill column” put this formula:

=ROUNDDOWN(amount/D9,0)

So what is happening here. Taken the amount mentioned in cell, divided by the first denomination and rounding it down to whole number. Actual catch is to understand the use of ROUNDDOWN and not ROUND function as we need to bring down the result any excess amount will be dealt with smaller denominations.

For example I have 76767 mentioned in amount then dividing it by 5000 will give 15.3534. Rounding down will give 15.

Step 4: In the amount column, simply multiply the # of bills and denomination to calculate the total amount with the following formula:

E9*D9

Step 5: In the second cell of “# of bills” column put this formula:

=ROUNDDOWN((amount-SUM($F$9:F9))/D10,0)

Don’t get overwhelmed by the length of formula. Concept is fairly easy. Out of the total amount mentioned, amount already covered by first (5000) denomination is being deducted. The result of which is then divided over the second denomination i.e. 1000 in our case.

Continuing our example of 76767, 75,000 is already covered by 5000 currency bills. Therefore, remaining amount is 1767. Dividing 1767 over 1000 will give 1.767 and rounding it down will result in “1”.

Step 6: Select the fill handle of # of bills column and drag it down to fill the range and also do the same with amount column so that cells are populated with appropriate formula.

Your bill calculator is ready! Here it is once again:

Easy as cake!

**global currency bill calculator**that can take any currency of any country. Salient features are:

- Easily select the country from the drop down to automatically get the appropriate denominations of that country
- Fully customizable, you can add your own currencies and particular denominations in the calculator by populating the denomination table
- Completely dynamic, calculator manages its shape automatically as denomination list changes from one country to another, it does not loses its cool!

Have a look at the following figure where I populate the currency table with new currency of Azerbaijan and it determines the currency bills automatically in a zing!

Other notable takeaways from the file include:

- Application of structured referencing system
- Dynamic Data validation list
- OFFSET function application
- And many other hidden bits!

supper, i want to know dependent auto denomination.

for example i have rs.500* 20 notes , the value written inbetween 1-20, plz send me the formula

Hi! I have the same question.

If I already have a list of notes from where I should denominate an amount, how can I limit or maximize the number of notes to the existing number?

Thank you

use min function

ex

=MIN(M3,ROUNDDOWN(($D$17-$H$14)/G3,0))

m3 is what we have the denomination

explain please

Dear Mr. Hassan,

Your contribution in spreading excel knowledge are much appreciated. I have a question: As accounting professionals, sometime, we need to calculate salaries of 10 or 20 persons and we have to withdraw from bank. Can this exercise help in calculation of 10 or 20 different amounts and with denomination of each amount in separate bills and then sums up total number of bills for all the amounts (Obviously with some modifications)

Using Offsets is not necessary. Index and match formula can also do the job.

My formula

=INDEX(Table1,ROWS($A$9:A9),MATCH($E$4,Table1[#Headers],0))

I found the solution. Change the ‘cents’ formula in the number of bills cell to: =ROUND((amount-SUM($F$9:F19))/D20,0). This seems to work with the dollar values that I’ve tried so far.

By the way, $2.00 bills do exist, but are no longer widely used or available.

Its Awesome, Thanks dear

Hi, wondering if you can help with my situation. Trying to determine start up cash for a fundraiser. assuming we have a bank of $4000, what’s the best way to break this money out into various denominations? Our items for sale are all priced in multiples of $5. Is there a logical formula for this or do folks normally just wing it?

Any Update Regarding Mr. John’s request, I’m Also looking an excel file for this Task, I’m Working in a NGO, we have to generate a report how much charity we have gathered and tally it with cash in hand, there should be an option to input how much Totals of each denomination we have than it should calculate it accordingly.

Your requirement is a little different one from John’s. What you are looking for is the reverse of this template. I will try to do it asap. Thanks for visiting and giving confidence!

Have you provided an example of this? I am searching for the exact solution. I would like to be able to change (lower or raise) the number of bills of any denomination and have the calculation adjust accordingly for the given amount.

This is awesome, my only issue is trying to use this example but letting the formula know that I only have a limited amount of each denomination, for example spreadsheet tells me I need 10 (£50 denominations) but I only have 6, is there an easy way to format this into the equation?

Oh that is really an interesting extension to this situation 🙂

See if we discuss we learn new things. I will try to write a follow up as P.S to the article this Sunday possibly.

Thanks again! Great idea!