Every country has different cash denominations, so you may need to calculate the number of different denominations based on the total amount. If it is just a small amount of cash, then you can calculate the different cash denominations manually, but if multiple cash amounts are needed to calculate the number of denominations, then it is not a good way to do it by manually.

This article will explain how to quickly calculate a given cash denomination value using formulas in a Microsoft Excel spreadsheet or Google Sheets.

1. General Formulas for Money Denomination Calculators

In Excel or Google Sheets, you can calculate the cash denomination number by using the following INT function and in combination with the SUMPRODUCT function to create a nested formula as shown below.

=INT(($A4-SUMPRODUCT($B$3:B$3,$B4:B4))/C$3)

For the denomination values in the first column, the following formula needs to be used.

Note: When you execute this formula, you need to make sure that iterative calculation is turned on, otherwise you will see the error message #VALUE!

To turn on the Iterative calculation, do the following：

For Microsoft Excel, you need to go to the Excel options window, then go to the Formulas category, and in the Calculation options area, check the box “Enable iterative calculation“. See the following screenshot.

For Google sheets, you need to select the File menu, then click on the Settings submenu, the “Settings for this Spreadsheet” window will pop up, click on the Calculation tab, set the Iterative calculation status to On, and then click on “saving settings“.

Let’s See How To Use This Formula

STEP1: Enter the following formula in cell B4 and press Enter to make the formula take effect.

=INT($A4/$B$3)

STEP2: Select cell B4, drag Fill Handler down to the other cells, so that other cells also apply the formula.

As you can see from the above figure, the value of the first denomination column has been calculated.

STEP3: Enter the following formula in cell C4 and press Enter to make the formula take effect.

=INT(($A4-SUMPRODUCT($B$3:B$3,$B4:B4))/C$3)

STEP4: Select cell C4, drag Fill Handler to the right to the other cells, so that other cells also apply the formula.

STEP5: Select the cell range C4:G4, drag the Fill Handler on cell G4 down to cell G8, so that other cells also apply the formula.

Let Me See How This Formula Works

=INT($A4/$B$3)

For the value in column B, since this column is in the first column of the entire denomination column, all we need to do is divide the denomination by Cash Amount, and after that we will remove the decimal part of the returned value by the INT function. For the value of column B will be much easier than other columns, because you do not need to consider the previous counting values.

For example, we can use the following simple formula for the cells in column B.

=INT($A4/$B$3)

=SUMPRODUCT($B$3:B$3,$B4:B4)

For column C and the subsequent columns, because you need to consider previous count of the denomination column, so you need to sum up the value of all the previous denomination column through the SUMPRODUCT function for a specific column, which contains two arrays, such as $B$3: B$3 used to specify the previous denomination column, $B4: B4 used to specify the number of denominations have been counted.

Here it should be noted that in the first array, the first cell range reference is absolute reference, the second cell range reference is mixed, so that you can lock a row, while the column will change.

=($A4-SUMPRODUCT($B$3:B$3,$B4:B4))/C$3

After that, the number of denominations in the current column is calculated by subtracting the original value from the denomination value returned by the SUMPRODUCT function for the previous columns and dividing it by the denomination value in the current column. Finally, the decimal part is removed by the INT function.

2. Video: How to Use Cash Denomination Calculator

In this video, you will learn how to create a Cash Denomination Calculator in Excel. You can use a simple spreadsheet that calculates the number of each denomination needed to make up a given cash amount.

3. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

4. Related Functions

• Excel INT function
  The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
• Excel SUMPRODUCT function
  The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…