Cash denomination calculator

In this example, the goal is to calculate the correct count for each denomination in the range D4:I4, given the inputs in column B. For example, for the amount 32, the result should be 1 x 20, 1 x 10, and 2 x 1, which add up to 32. The calculation for each denomination is quite easy and can be done with the INT function like this:

=INT(amount/denomination)

After dividing the amount by a denomination, the INT function returns only the integer portion of the result. This gives us the number of whole denominations that fit into the amount. But that's not the whole picture for this problem. The challenge is we need to account for the denominations already counted before we count the next smallest denomination. In other words, we must reduce the amount as we move from left to right by the value of all denominations already counted.

This article explores two ways to solve this problem. First, we look at a traditional approach with SUM, INT, and specially constructed cell references. Next, we look at a modern dynamic array formula based on the SCAN function.

SUM and INT option

One way to solve this problem is with the SUM function and the INT function, and several carefully configured cell references. In the example shown above, the formula in cell D5 looks like this"

=INT(($B5-SUM($C$4:C$4*$C5:C5))/D$4)

Note: this is an array formula in Excel 2019 and older and must be entered with control + shift + enter. In a more recent version of Excel, the formula will work without special handling.

Notice the following:

• $B5 - a mixed reference to lock the column
• $C$4:C$4 - a mixed and expanding reference
• $C5:C5 - an expanding reference
• Column C is blank, yet we include it in the formula

The core idea is this: for each denomination in row 4, we want to calculate a count. The trick is that we also need to account for any denominations already counted. Working from the inside out, we begin with the original amount in column B, then subtract the value of the denominations already counted:

=$B5-SUM($C$4:C$4*$C5:C5)

The tricky bit is the expanding ranges inside SUM. The first, $C$4:C$4, refers to the denominations in row 4. The second, $C5:C5, references the count of any previously counted denominations. As these formulas are copied to the right, these ranges will expand. Inside SUM, we multiply the denominations by their counts to calculate the value of all previous denominations. Then, the SUM function calculates a total, which is subtracted from the original amount in B5.

Next, we divide the adjusted amount by the numeric denomination in row 4 and feed the result into the INT function, which returns the whole number after division:

=INT(($B5-SUM($C$4:C$4*$C5:C5))/D$4)

When the denomination does not fit into the amount, the result from INT is zero. When the denomination does fit, INT returns a count as a whole number. As the formula is copied to the right, the expanding references expand to account for the value of previously counted denominations, and the same calculation is repeated in each column. When the formulas are copied down, the correct denominations are calculated for each amount in column B.

Checking the result

To check your results, you can add this formula to the right of the table:

=SUMPRODUCT($D$4:$I$4,D5:I5)

In each row, SUMPRODUCT multiplies the counts by the denominations in row 4 and returns a sum that should always equal the original amount in column B.

Note: The formula above uses INT, but you could also use the FLOOR function instead.

Dynamic array formula option

This seems like exactly the kind of problem that could be solved neatly with a modern dynamic array formula and, in particular, a formula that uses the SCAN function. I had a crack at this and came up with the following formula:

=LET(
amount,B5,
denoms,$D$4:$I$4,
remainders,SCAN(amount,denoms,LAMBDA(a,v,MOD(a,v))),
available_amounts,DROP(HSTACK(amount,remainders),,-1),
INT(available_amounts/denoms)
)

We use the LET function to assign variable names and improve readability. At a high level, the formula works like this:

• To calculate counts correctly, we need to divide the amount by each denomination in sequence. Before we divide, we need to subtract the value of the denominations already counted.
• We use the SCAN function with the MOD function to calculate remainders. While SCAN gives us the correct remainders going forward, they are not aligned correctly with denominations.
• We construct an array of "available amounts". This is the remaining amount available for each denomination as we move across the table. Because there is no "previous remainder" for the first column, we do some fancy footwork with DROP and HSTACK to insert the original amount first, then drop the last remainder, which we don't need. Essentially, we are aligning the correct amounts with each denomination.
• Finally, in the last step, we use INT to calculate a whole number after dividing the available amounts by each denomination. You can see the result below:

Despite the fact that this formula is longer than the original option, there are notable advantages:

• Each row requires one formula only instead of six.
• We don't have to work with a tangle of mixed and expanding references.
• The variable names make it easier to understand the flow of the formula.
• We don't need to use an empty column in the calculation.

As always, there are many ways to solve a problem with Excel's latest dynamic array functions. Let me know if you have a more elegant solution!