How to divide a number into various denominators eg thousands, hundreds,
tens, fives & ones for example 5527 can be divided into 5 (thousand), 5
(hundreds), 2(tens) & 7 (ones).

There's probably an easier way but try this with your number in A1:-

=INT(A1/1000)&" (Thousands) "&INT(RIGHT(A1,3)/100)& " (Hundreds)
"&INT(RIGHT(A1,2)/10)& " (Tens) "&INT(RIGHT(A1,1))& " (Ones)"

I haven't allow for decimals portions.

Mike

"maa" wrote:

How to divide a number into various denominators eg thousands, hundreds,
tens, fives & ones for example 5527 can be divided into 5 (thousand), 5
(hundreds), 2(tens) & 7 (ones).

This uses a macro. Is that ok?

I put these values in B1:M1 (12 cells):
1000, 100, 50, 20, 10, 5, 1, 0.5, 0.25, 0.1, 0.05, 0.01

($1000 bill, $100 bill, $50, ..., thru a penny (.01))

I put this in A2: 1234.32

Then I added this function from Chip Pearson into a General module.

Option Explicit
Function ConvertToCurrency(ByVal Amt As Double) As Variant
Dim Ndx As Integer
Dim Counter As Integer
Dim Arr As Variant
Arr = Array(1000, 100, 50, 20, 10, 5, 1, 0.5, 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

Then I selected B2:M2 and typed:
=converttocurrency(a2)
but instead of hitting enter, I hit ctrl-shift-enter.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you need more currency ($500), just add it to your headers, put it in the
code and select as many cells as required (12 will become 13 in my example).

Remember to hit ctrl-shift-enter to enter the formula.

maa wrote:

How to divide a number into various denominators eg thousands, hundreds,
tens, fives & ones for example 5527 can be divided into 5 (thousand), 5
(hundreds), 2(tens) & 7 (ones).

--

Dave Peterson

On Mon, 4 Jun 2007 04:38:00 -0700, maa wrote:

How to divide a number into various denominators eg thousands, hundreds,
tens, fives & ones for example 5527 can be divided into 5 (thousand), 5
(hundreds), 2(tens) & 7 (ones).

If you want to divide the 5527 into the values you mention, you would have,
instead of 7 (ones), 1(five) and 2 (ones).

In any event, for integers as you indicate, you can do the following.

Set up your top row as follows:

B1: $1,000
C1: $100
D1: $10
E1: $5
F1: $1

A2: $5,527

B2: =INT($A2/B$1)
C2: =INT(($A2-SUMPRODUCT($B$1:B$1,$B2:B2))/C$1)

Fill right C2:F2

Then fill down B2:F2 as far as you require.

If "cents" will be used, then add the value of the coins you want to use to
G1:x1

e.g. G1: $0.50
H1: $0.25

etc.

and change the formula in C2:

=INT(ROUND(($A2-SUMPRODUCT($B$1:B$1,$B2:B2))/C$1,2))

to take care of occasional issues with Excel's handling of decimal numbers.

Fill right and down as before.
--ron