Money conversion formula

[Vera Creque]

Does anyone have a formula that will calculate the number of bills and
coins, given a dollar amount?

Example

$177.85 =
1,$100
1, $50
1, $20
1, $5
2, $1
3, quarters
1, dime

[Stephen]

Vera Creque <vera....@wheaton.com> wrote in message
news:391AD429...@wheaton.com...

Vera,

I once did this in a table; I don't know if there's a better way. Anyway, I
will describe what I did.

Start with your amount (177.85) in C1.
In A2 type your largest note (sorry, bill - I'm in the UK!) value (100).
In B2 put the formula =INT(C1/A2) which will give the number of these bills.
In C2 put the formula =ROUND(C1-A2*B2,2) which gives the remaining amount.
From A3 downwards list your smaller bill/coin values in descending order.
Copy the formulas from B2:C2 down as far as your bill/coin list goes, and
your answers are in column B.

Stephen

[Chip Pearson]

Vera,

Here's a simple VBA function that will return an array of 10 items. The
elements of the array indicate the number of bills and or coins to make the
input amount.

It returns the counts for $100 bills, $50, $20, $10, $5, $1, quarters,
dimes, nickels, and pennies, so the array has 10 elements. To use it in a
worksheet, select a range of 10 cells, e.g., D2:M2, type
=ConvertToCurrency(A2) and press Ctrl+Shift+Enter. Because the function
returns an array, you *must* press Ctrl+Shift+Enter rather than just Enter
when you first enter the formula and whenever you edit it later.

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.5, 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

By the way, your question has nothing to do with Links in Excel, so you'd be
more likely to get answers by posting the Misc or Programming newsgroup,
instead of Links.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services
www.cpearson.com ch...@cpearson.com