Calculate a customer's change in cash

To show the total amounts of each denomination enter: =C3*D3 in cell E3 and drag down to cell E14. In cell E15 enter: =SUM(E3:E14)

This total is a check that the amount entered with the spinner agrees with the total of notes and coins selected.

In this worksheet, formatting is unusually important. Cells B1, E3 and E15 are formatted as Currency with two decimal places. Cell B15 is the default General format. Cells E4:E14 are formatted as Number with two decimals, while the ranges C3:C8 and D3:D14 are formatted Number with no decimals. Cells C9:C14 are Custom formatted .00.

Of course there are other formulas that can be used to solve this problem. You could use MROUND and IF statements, but the TRUNC function is the simplest.

Whatever Dan’s need for this, there are numerous other uses of the application. For a school shop you could enter the value of a note tendered and the cost of an item, then twiddle the spinner to find the notes and/or coins to be given in change.

Sorting the sheets
Every now and then a reader asks how to sort the worksheets alphabetically in an Excel workbook. You can do this with a simple macro. Just press Alt & F11 to open the VBA for Excel Editor. Choose Insert, Module and type the following into the right-hand panel:
Sub SortSheets()
Dim a As Integer, b As Integer, x As Integer
x = Sheets.Count
On Error GoTo Trap:
For a = 1 to x - 1
For b = a + 1 To x
If Sheets(b).Name < Sheets(a). Name Then
Sheets(b).Move Before:=Sheets(a)
End If
Next
Next
Sheets(1).Select
Trap:
End Sub