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?

How 2 breakdown $$$ into $ $ $?
 

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?

How 2 breakdown $$$ into $ $ $?
 

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?