Determine number of currency bills or notes for any amount – Currency bill Calculator [How To]

 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 generator/calculator

Taking this concept further, I made a global currency bill calculator that can take any currency of any country. Salient features are:

1. Easily select the country from the drop down to automatically get the appropriate denominations of that country
2. Fully customizable, you can add your own currencies and particular denominations in the calculator by populating the denomination table
3. 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!