Hi - Dave here.
Happy Friday!
This week, I want to explain how to build a "cash denomination calculator." A cash denomination calculator is a tool for counting and verifying cash amounts.
You can see an example in the worksheet below. The idea is to calculate the correct counts for the denominations in row 4, given the amounts in column B.
For example, the counts for the amount $32 in cell B9 should be 1 x $20, 1 x $10, and 2 x $1, which add up to $32.
[
Download the workbook and read the full explanation]
The article explains two ways to solve this problem. On Sheet1, I use a traditional approach using the SUM and INT functions with very carefully constructed cell references. On Sheet2, I use a modern dynamic array formula based on the SCAN function. The workbook also includes two more sheets to show how to extend the formulas to handle coins. Download the workbook and try it out yourself.
Note: The first approach will work in any version of Excel. The second approach requires Excel 2021 or later.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem with an Excel formula. You can sign up on our home page.