Cash Denomination Calculator

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.

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

Cash denomination calculator2

Let’s See How To Use This Formula

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

=INT($A4/$B$3)
Cash denomination calculator

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

Cash denomination calculator

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

STEP 3: 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)

Cash denomination calculator

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

Cash denomination calculator

STEP 5: 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.

Cash denomination calculator

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)
Cash denomination calculator

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

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],…)…
Related Posts

Check Cell If Contains One of Many with Exclusions

In Microsoft Excel Spreadsheet or google sheets, when cells contain multiple strings, how can we accomplish this task if you want to check whether these cells contain more than one given string and exclude other given strings? In this article, ...

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Count Cells that are Case Sensitive

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Check Dates in chronological order

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel's Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Sidebar