Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
Here's an ingeious user defined array function written by Chip Pearson.
Function ConvertToCurrency(ByVal Amt As Double) As Variant Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function It converts a dollar¢s value to the number of 100 dollar bills, 50s, 20s, 10s, 5s, 1s, 25c, 10c, 5c, and pennies. Put a value in A1 and then select A2:J2 and type this in: =converttocurrency(a1) and hit control-shift-enter. But suppose the actual coins and notes available are limited in their quantities. For example, suppose in the notes and coins on hand there is only one $1 bill available. If the sum to be split up is $3. We need a result like: $1 x 1, 25c x 8. How can we tell converttocurrency what quantities of notes and coins are available? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to distribute coins to achieve minimum difference? | Excel Discussion (Misc queries) | |||
getting quantities according to date | Excel Worksheet Functions | |||
if duplicates then add quantities? | Excel Programming | |||
Different quantities of a certain item... | Excel Worksheet Functions | |||
sum partnumber quantities | Excel Programming |