Intermediate Exercise
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).
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 + + to replicate to the others.
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
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
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.
© 2019 SuperExcelVBA | ABOUT
Thank you for contributing. A message was sent reporting your comment.