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&cents 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?

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&cents 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?

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&cents 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?

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&cents 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?

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&cents 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?

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&cents 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?

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&cents 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?

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&cents 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?

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&cents 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?