How 2 breakdown $$$ into $ $ $?

I'm looking for a way to have Excel take a given amount of money (say $365)
and break it down into individual bills, largest denomination to smallest (3
hundred-dollar bills, 1 fifty-dollar bill, 1 ten-dollar bill, 1 five-dollar
bill.)

The initial dollar amount is the result of a formula in a spreadsheet I
currently use. I tried rounding using the following formula:

=ROUND(D2,-2), where D2 is the cell containing the initial dollar amount.

But this has 2 problems: 1) It only works for hundreds, and 2) It rounds up
for anything over $49 (so $365 results in 4 hundred dollar bills.)

Some additional info...
- I'm only working with whole numbers in multiples of 5. In other words,
I'll never need to break down $361.77, it would be $360.
- The results can either be in "number of bills" or "total dollar amount."
For example, either "3" or "$300" for hundreds is fine.

Hope I made myself clear. Any suggestions?

Here's one way:

For a value in A1

B1: 100
B2: 50
B3: 20
B4: 10
B5: 5

C1: =TRUNC($A$1/B1)
C2: =TRUNC(($A$1-SUMPRODUCT($B$1:B1,$C$1:C1))/B2)
Copy that formula down thru C5


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"mjpage" wrote:

> I'm looking for a way to have Excel take a given amount of money (say $365)
> and break it down into individual bills, largest denomination to smallest (3
> hundred-dollar bills, 1 fifty-dollar bill, 1 ten-dollar bill, 1 five-dollar
> bill.)
>
> The initial dollar amount is the result of a formula in a spreadsheet I
> currently use. I tried rounding using the following formula:
>
> =ROUND(D2,-2), where D2 is the cell containing the initial dollar amount.
>
> But this has 2 problems: 1) It only works for hundreds, and 2) It rounds up
> for anything over $49 (so $365 results in 4 hundred dollar bills.)
>
> Some additional info...
> - I'm only working with whole numbers in multiples of 5. In other words,
> I'll never need to break down $361.77, it would be $360.
> - The results can either be in "number of bills" or "total dollar amount."
> For example, either "3" or "$300" for hundreds is fine.
>
> Hope I made myself clear. Any suggestions?

Ron,

Yes, that DOES help. You gave me exactly the formulas that I needed. Thank
you!

"Ron Coderre" wrote:

> Here's one way:
>
> For a value in A1
>
> B1: 100
> B2: 50
> B3: 20
> B4: 10
> B5: 5
>
> C1: =TRUNC($A$1/B1)
> C2: =TRUNC(($A$1-SUMPRODUCT($B$1:B1,$C$1:C1))/B2)
> Copy that formula down thru C5
>
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "mjpage" wrote:
>
> > I'm looking for a way to have Excel take a given amount of money (say $365)
> > and break it down into individual bills, largest denomination to smallest (3
> > hundred-dollar bills, 1 fifty-dollar bill, 1 ten-dollar bill, 1 five-dollar
> > bill.)
> >
> > The initial dollar amount is the result of a formula in a spreadsheet I
> > currently use. I tried rounding using the following formula:
> >
> > =ROUND(D2,-2), where D2 is the cell containing the initial dollar amount.
> >
> > But this has 2 problems: 1) It only works for hundreds, and 2) It rounds up
> > for anything over $49 (so $365 results in 4 hundred dollar bills.)
> >
> > Some additional info...
> > - I'm only working with whole numbers in multiples of 5. In other words,
> > I'll never need to break down $361.77, it would be $360.
> > - The results can either be in "number of bills" or "total dollar amount."
> > For example, either "3" or "$300" for hundreds is fine.
> >
> > Hope I made myself clear. Any suggestions?