note/coinage converter???

[sheila]

is there such a thing?

a wages program i have just finished for a client needs a little
polish and i thought a coinage/note converter would be a cool idea.
is there a simple formula to convert say A1 ($458.00) to a
note/coinage breakdown? the breakdown only requires full dollar
amounts, not coins, as the figure in A1 will only ever be full dollar
amounts.

also, heres another little twist. i need something that calculates
the 'full dollar' amount. i know about roundup/down and thats cool,
but my client (cheap that he is) says if he rounds up one week, he
should round down the following week so he is not out of pocket.

any ideas here?

would really appreciate (as always) any help i can get.

thanks guys

sheila

^_^

[Bernie Deitrick]

Sheila,

What do you mean by a coinage/note converter? What result would $458.00
give?

Bernie

[Tony Rice]

I know what she means:

4 x $100 bills
1 x $ 50 bill
1 x $5
3 x $1

Is that right Sheila?

Tony Rice

Bernie Deitrick <dei...@consumer.org> wrote in message
news:393E6499...@consumer.org...

[John McCormack]

Hello Sheila,

This works for me:

With the cheque amount £458 in cell A2, and cell B1 to G1 contains the
denominations such as £100, £50, £20, £10, £5, £1
then cell B2 = INT($A$2/B1) gives 4 £100 notes
cell C2 = INT(($A$2-SUMPRODUCT($B1:B1,$B2:B2))/C1) giving 1 £50 note
cell C2 can be copied across to G2 and the references will adjust
automatically

You might be able to roundup / roundown by including an if statement to
look at the ISO week number - if it is odd round up , if even round down

Hope this is of some use to you

Regards

John McC

sheila <sheilas1@hot_m_a_il.com> wrote in article
<qg4sjsk6enkgrgie7...@4ax.com>...

[Chip Pearson]

Sheila,

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.0001) >= Arr(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function

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

"sheila" <sheilas1@hot_m_a_il.com> wrote in message
news:qg4sjsk6enkgrgie7...@4ax.com...

[chris...@zeronet.co.uk]

With a small amount of work this seems to be straight forward.

A row of cells would contain the note/coin denominations.

Then another cell referring to the wage would check how many times the wage
was divisible by the largest note/coin. This would leave a whole number and
a possible remainder. Excel has functions for both of those. Simply repeat
them along the row of denominations until the remainder is zero.

Tony Rice <Production...@Mweb.co.za> wrote in message
news:Ob81NRJ0$GA....@cppssbbsa02.microsoft.com...

> I know what she means:
>
> 4 x $100 bills
> 1 x $ 50 bill
> 1 x $5
> 3 x $1
>
> Is that right Sheila?
>
> Tony Rice
>
> Bernie Deitrick <dei...@consumer.org> wrote in message
> news:393E6499...@consumer.org...
> > Sheila,
> >
> > What do you mean by a coinage/note converter? What result would $458.00
> > give?
> >
> > Bernie
> >
> > sheila wrote:
> >

[Dave Peterson]

Watch out for that nickel. I think that Chip wanted .05 in between dime and
penny.

(ps. I thought this was another neat function when you posted before!)

> Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.5, 0.01)

***

> > is there such a thing?
> >
> > a wages program i have just finished for a client needs a little
> > polish and i thought a coinage/note converter would be a cool idea.
> > is there a simple formula to convert say A1 ($458.00) to a
> > note/coinage breakdown? the breakdown only requires full dollar
> > amounts, not coins, as the figure in A1 will only ever be full dollar
> > amounts.
> >
> >
> > also, heres another little twist. i need something that calculates
> > the 'full dollar' amount. i know about roundup/down and thats cool,
> > but my client (cheap that he is) says if he rounds up one week, he
> > should round down the following week so he is not out of pocket.
> >
> > any ideas here?
> >
> > would really appreciate (as always) any help i can get.
> >
> > thanks guys
> >
> > sheila
> >
> > ^_^

--

Dave Peterson
pete...@freewwweb.com

[Chip Pearson]

> Watch out for that nickel. I think that Chip wanted .05 in between dime
and
> penny.

Yes, I think I did. Nice catch, Dave. I'll do anything to make an extra
nickel! A nickel here, a nickel there, pretty soon it is real money.

Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.5, 0.01)

should be

Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)

Thanks!

> (ps. I thought this was another neat function when you posted before!)

Thank you. I was a bit proud of myself.

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

"Dave Peterson" <pete...@freewwweb.com> wrote in message
news:393EDC15...@freewwweb.com...

[David McRitchie]

So someone gets .09 in pennies instead of .04 in pennies,
nobody gained or lost a cent. You'll have to do better than
that to gain a nickle in a Make.Money.Fast scheme.

[sheila]

thanks to all, but john, your one was simple and perfect. i
appreciate the help.

^_^

sheila

On Wed, 07 Jun 2000 16:46:44 GMT, "John McCormack"
<mccor...@eircom.net> wrote:

>Hello Sheila,
>
>This works for me:
>
>With the cheque amount £458 in cell A2, and cell B1 to G1 contains the
>denominations such as £100, £50, £20, £10, £5, £1
>then cell B2 = INT($A$2/B1) gives 4 £100 notes
> cell C2 = INT(($A$2-SUMPRODUCT($B1:B1,$B2:B2))/C1) giving 1 £50 note
> cell C2 can be copied across to G2 and the references will adjust
>automatically
>
>
>You might be able to roundup / roundown by including an if statement to
>look at the ISO week number - if it is odd round up , if even round down
>
>Hope this is of some use to you
>
>Regards
>
>John McC
>
>
>
>sheila <sheilas1@hot_m_a_il.com> wrote in article
><qg4sjsk6enkgrgie7...@4ax.com>...

[Gotjoy!]

Hi Chip,

I've found this VBA function to be very helpful despite my being totally new to macros.
However, my currency also includes a $2 note and issuing of the 1 cent coin has been discontinued. How can the VBA function be modified to adapt to these changes?

Thanks much!