Explanation
In this example, the goal is to build a “cash denomination calculator.”
A cash denomination calculator is a tool for counting and verifying cash amounts.
It can calculate the denominations needed to achieve a certain cash value.
This article explores two ways to solve this problem.
First, we look at a traditional approach using SUM, INT, and carefully constructed cell references.
Next, we look at a moderndynamic array formulabased on the SCAN function.
Both approaches are designed to calculate all denominations for a given amount with the same basic formula.
The calculations occur in the range D5:I16.
Note: the denomination amounts can be changed to handle different currencies.
it’s possible for you to also add columns to handle coins.
For a given denomination, this gives us the number of whole denominations that fit into the amount.
In Excel 2021+, the formula will work without special handling.
The first, $C$4:C$4, refers to the denominations in row 4.
The second, $C5:C5, refers to previously counted denominations.
Inside SUM, we multiply the denominations by their counts to calculate thevalueof previous denominations.
The SUM function calculates a total, which is subtracted from the original amount in B5.
As the formula is copied to the right, these ranges will expand to accommodate more previous counts.
Why we are starting with column C, which is empty?
After the above calculations, we have the remaining amount.
When the denomination does fit, INT returns a count as a whole number.
When the formulas are copied down, the correct denominations are calculated for each amount in column B.
Let me know if you have a more elegant solution!
See below for details about why rounding is important when working with fractional values like (some) coins.
At that point, we’ve accounted for 15.35, so the formula should return 2 for pennies.
However, the formula instead returns 1 instead of 2.
This happens because15.37 - 15.35evaluates to0.0199999999999996instead of0.02due to how Excel handles floating-point arithmetic.
Computers store numbers in a binary format.
can be represented exactly.
Decimal fractions like 15.35 and 15.37 cannot be represented exactly in binary.
Instead, they are approximated by the closest representable binary numbers.
Most of the time, you won’t notice this because Excel will display a number like0.0199999999999996as0.02by default.
The solution above uses the ROUND function to round off the intermediate results, eliminating most floating-point problems.
Related functions
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
Note that negative numbers becomemore negative.
For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
LET Function
The Excel LET function lets you define named variables in a formula.
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
The number of rows and columns to remove is provided by separaterowsandcolumnsarguments.
Each subsequent array is appended to the right of the previous array….
