Calculate a customer's change in cash

Reader Dan Cutforth wrote in to say: “I would like a spreadsheet that calculates the most efficient combination of notes and coins to make up an entered cash amount. Ideally the amount would be selected with a spinner, though I’m using Excel 2007 and the old controls on a Forms toolbar don’t appear to be available.”

The latest version of Excel doesn’t offer such design facilities by default. But they are easy to add. Click the Office icon and then the Excel Options button. Choose Popular and under Top Options you’ll find a box you can check next to, Show Developer tab in the Ribbon.

Open a new workbook. Click the now visible Developer tab. Click the Insert icon in the Control section. From the now-displayed Form Controls box pick the spinner.

In versions prior to Excel 2007 you just display the Forms toolbar and click on the spinner icon. In all versions, a small cross appears. Draw a rectangle over the cell where you want the spinner to be. Right-click on the spinner and choose Format Control.

In the Format Object dialogue box, under the Control tab, you can enter the starting value, the minimum amount that can be chosen and the maximum amount.

The incremental change amount you want is 1, which is the default. Enter the address of any cell that’s out of the way to be the link cell.

Under the Properties tab of this dialogue box you can choose whether the spinner is to be sized and moved with the cell or not, and if the spinner is to be shown on printouts or not.

Enter the address of the link cell in a cell above or beside the spinner. Divide by 100 so the coins can be calculated in pennies; in other words, if the link cell is B15 enter: =B15/100 in this cell.

The formula in the link cell may be displayed, but you could format it using a white font on white background to hide it.

To follow our example enter the note and coin denominations in column C starting with cell C3. All you have to do now is enter three variations of a short formula that will calculate how many of each note and coin is needed to make up the selected amount.

If the selected amount appears in cell B1, in cell D3 enter: =TRUNC($B$1/C3,0)

In cell D4 enter: =TRUNC(($B$1-SUM($E$3:E3))/C4,0)

Point to the Fill Handle in the lower-right of cell D4 and drag down to cell D13. The second argument of the TRUNC function is a number specifying the precision of the truncation. For calculating the single pennies this argument needs to be increased so in cell D14 enter:
=TRUNC(($B$1-SUM($E$3:E13))/C14,1)