Count dollar bills

Intermediate Exercise

Statement

Create a Function that receives an integer as input and returns as few dollar bills as possible (100, 50, 20, 10, 5, 2, and 1) to compose it. The result should be expressed in 7 separate cells in the worksheet (one for each bill).

Suggested Concepts:

Dim
Array
Int - Return the integer portion of a number

Remember that in most cases there is more than one way to solve the exercise.

Hint: Once the function is done, you can select the seven cells into which the Function will be inserted, place the function in the first one and press Ctrl + Shift + Enter to replicate to the others.

Solved Exercise

Exercise Solution

First we define the argument of the function and its output
```
Function NoTeS(X As Integer) As Variant
```

Create the variables, on for each bank note and one to be the remainder

Dim N100 As Integer
Dim N50 As Integer
Dim N20 As Integer
Dim N10 As Integer
Dim N5 As Integer
Dim N2 As Integer
Dim N1 As Integer
Dim Rest As Double

Remain = X

We calculate the integer part of the division of the input for each possible note value, starting with the largest (100), and gradually taking its remainder to the lowest (since we are trying to minimize the number of notes)

N100 = Int(Remain / 100)
Remain = Remain - (N100 * 100)
N50 = Int(Remain / 50)
Remain = Remain - (N50 * 50)
N20 = Int(Remain / 20)
Remain = Remain - (N20 * 20)
N10 = Int(Remain / 10)
Remain = Remain - (N10 * 10)
N5 = Int(Remain / 5)
Remain = Remain - (N5 * 5)
N2 = Int(Remain / 2)
Remain = Remain - (N2 * 2)
N1 = Remain

To leave the answer clearer we chose to show it as a vector, where each element is equivalent to a quantity of a given bank note

NoTeS = Array(N100, N50, N20, N10, N5, N2, N1)

'NoTeS = N100 & "(100)" & N50 & "(50)" & N20 & "(20)" & N10 & "(10)" & N5 & "(5)" & N2 & "(2)" & N1 & "(1)"
'If the answer were given in a single line

Consolidated Answer

Function NoTeS(X As Integer) As Variant

    Dim N100 As Integer
    Dim N50 As Integer
    Dim N20 As Integer
    Dim N10 As Integer
    Dim N5 As Integer
    Dim N2 As Integer
    Dim N1 As Integer
    Dim Remain As Double

    Remain = X

    N100 = Int(Remain / 100)
    Remain = Remain - (N100 * 100)
    N50 = Int(Remain / 50)
    Remain = Remain - (N50 * 50)
    N20 = Int(Remain / 20)
    Remain = Remain - (N20 * 20)
    N10 = Int(Remain / 10)
    Remain = Remain - (N10 * 10)
    N5 = Int(Remain / 5)
    Remain = Remain - (N5 * 5)
    N2 = Int(Remain / 2)
    Remain = Remain - (N2 * 2)
    N1 = Remain

    NoTeS = Array(N100, N50, N20, N10, N5, N2, N1)

    'NoTeS = N100 & "(100)" & N50 & "(50)" & N20 & "(20)" & N10 & "(10)" & N5 & "(5)" & N2 & "(2)" & N1 & "(1)"
    'If the answer were given in a single line
End Function

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED