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?

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

Thanks Tom.

Is it possible to populate arr1 from a range in Sheet1?

Max


"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:u$yh%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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?
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:u$yh%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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?
>>
>
>

Sorry Tom, I don't fully understand.
If the available quantities are in the Range("A3:J3")

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:e$$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:u$yh%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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?
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sorry Tom, I don't fully understand.
> If the available quantities are in the Range("A3:J3")
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:e$$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:u$yh%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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?
>>>>
>>>
>>>
>>
>>
>
>

If calling from VBA

Dim v as variant

v = ConvertToCurrency(Range("B5").Value, Range("A3:J3"))

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:O%23h8%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Sorry Tom, I don't fully understand.
>> If the available quantities are in the Range("A3:J3")
>>
>> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
>> news:e$$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:u$yh%(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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?
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:O%23h8%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Sorry Tom, I don't fully understand.
>> If the available quantities are in the Range("A3:J3")
>>
>> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
>> news:e$$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:u$yh%(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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?
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
> news:O%23h8%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Sorry Tom, I don't fully understand.
> >> If the available quantities are in the Range("A3:J3")
> >>
> >> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> >> news:e$$(E-Mail Removed)...
> >>> 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" <(E-Mail Removed)> wrote in message
> >>> news:u$yh%(E-Mail Removed)...
> >>>> 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" <(E-Mail Removed)> wrote in message
> >>>> news:(E-Mail Removed)...
> >>>>> 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?
> >>>>>
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>
>
>