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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
Thanks Tom.
Is it possible to populate arr1 from a range in Sheet1? Max "Tom Ogilvy" wrote in message ... 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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant
Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Dim Arr1 as Variant, i as Long Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01) Arr1 = Arr i = lbound(arr) for each cell in rng Arr1(i) = cell.value i = i + 1 Next ' Available quantities of the above 'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
Sorry Tom, I don't fully understand.
If the available quantities are in the Range("A3:J3") "Tom Ogilvy" wrote in message ... Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Dim Arr1 as Variant, i as Long Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01) Arr1 = Arr i = lbound(arr) for each cell in rng Arr1(i) = cell.value i = i + 1 Next ' Available quantities of the above 'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
select 10 cells in the same row,
in the formula bar enter =ConvertToCurrency(B5,A3:J3) commit with Ctrl+shift+Enter rather than just enter. B5 contains the amount in dollars and cents (3 in your example) A3:J3 holds the quantities corresponding to $100, $50, $20, etc Worked fine for me. -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... Sorry Tom, I don't fully understand. If the available quantities are in the Range("A3:J3") "Tom Ogilvy" wrote in message ... Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Dim Arr1 as Variant, i as Long Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01) Arr1 = Arr i = lbound(arr) for each cell in rng Arr1(i) = cell.value i = i + 1 Next ' Available quantities of the above 'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
If calling from VBA
Dim v as variant v = ConvertToCurrency(Range("B5").Value, Range("A3:J3")) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... select 10 cells in the same row, in the formula bar enter =ConvertToCurrency(B5,A3:J3) commit with Ctrl+shift+Enter rather than just enter. B5 contains the amount in dollars and cents (3 in your example) A3:J3 holds the quantities corresponding to $100, $50, $20, etc Worked fine for me. -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... Sorry Tom, I don't fully understand. If the available quantities are in the Range("A3:J3") "Tom Ogilvy" wrote in message ... Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Dim Arr1 as Variant, i as Long Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01) Arr1 = Arr i = lbound(arr) for each cell in rng Arr1(i) = cell.value i = i + 1 Next ' Available quantities of the above 'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and coins are available?
Tom it works beautifully, thank you.
What I didn't understand was that I had to put the range in the brackets. Max "Tom Ogilvy" wrote in message ... select 10 cells in the same row, in the formula bar enter =ConvertToCurrency(B5,A3:J3) commit with Ctrl+shift+Enter rather than just enter. B5 contains the amount in dollars and cents (3 in your example) A3:J3 holds the quantities corresponding to $100, $50, $20, etc Worked fine for me. -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... Sorry Tom, I don't fully understand. If the available quantities are in the Range("A3:J3") "Tom Ogilvy" wrote in message ... Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Dim Arr1 as Variant, i as Long Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01) Arr1 = Arr i = lbound(arr) for each cell in rng Arr1(i) = cell.value i = i + 1 Next ' Available quantities of the above 'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can we tell converttocurrency what quantities of notes and
It is best to put dependencies in the argument of the function. That is how
Excel determines that it needs to recalculate the function when a cell upon which it is dependent is changed. If I just hardcoded a range in the function itself, then changing the quantity available would not cause the function to recalculate. -- Regards, Tom Ogilvy "Max Bialystock" wrote: Tom it works beautifully, thank you. What I didn't understand was that I had to put the range in the brackets. Max "Tom Ogilvy" wrote in message ... select 10 cells in the same row, in the formula bar enter =ConvertToCurrency(B5,A3:J3) commit with Ctrl+shift+Enter rather than just enter. B5 contains the amount in dollars and cents (3 in your example) A3:J3 holds the quantities corresponding to $100, $50, $20, etc Worked fine for me. -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... Sorry Tom, I don't fully understand. If the available quantities are in the Range("A3:J3") "Tom Ogilvy" wrote in message ... Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Dim Arr1 as Variant, i as Long Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01) Arr1 = Arr i = lbound(arr) for each cell in rng Arr1(i) = cell.value i = i + 1 Next ' Available quantities of the above 'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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) ' Available quantities of the above arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Regards, Tom Ogilvy "Max Bialystock" wrote in message ... 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? |
Reply |
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 |