Hi all

This is my problem:


I need a breakdown of a number that the user types in in cell A1, i
terms of 1000's and 500's and 100s and 10's and 1's

E.g 7551 would be

1000's = 7
500's = 1
100's = 0
10's = 5
1's = 1

and

786 would be

1000's = 0
500's = 1
100's = 2
10's = 8
1's = 6

and 11655 would be


1000's = 11
500's = 1
100's = 1
10's = 5
1's = 5



Any ideas? Many thanks al

--
HUBBUB8
-----------------------------------------------------------------------
HUBBUB88's Profile: http://www.excelforum.com/member.php...fo&userid=2441
View this thread: http://www.excelforum.com/showthread.php?threadid=38014

On Fri, 17 Jun 2005 11:55:38 -0500, HUBBUB88
wrote:


Hi all

This is my problem:


I need a breakdown of a number that the user types in in cell A1, in
terms of 1000's and 500's and 100s and 10's and 1's


This seems to work, but not extensively tested.

Set up a worksheet as follows:

C1: 1000
D1: 500
E1: 100
F1: 10
G1: 1

C2: =INT(Num/C1)
D2: =INT((Num-INT(SUMPRODUCT($C$1:C1,$C$2:C2)))/D1)

then copy/drag the formula in D2 across to G2.


--ron

For cell A1, these formulas return 1000's, 500's, 100's, 10's, and 1'
respectively:

=INT(A1/1000)
=INT(MOD(A1,1000)/500)
=INT(MOD(A1,500)/100)
=INT(MOD(A1,100)/10)
=MOD(A1,10)

Does that help?

Regards,
Ro

--
Ron Coderr
-----------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...fo&userid=2141
View this thread: http://www.excelforum.com/showthread.php?threadid=38014

On Fri, 17 Jun 2005 12:14:27 -0500, Ron Coderre
wrote:


For cell A1, these formulas return 1000's, 500's, 100's, 10's, and 1's
respectively:

=INT(A1/1000)
=INT(MOD(A1,1000)/500)
=INT(MOD(A1,500)/100)
=INT(MOD(A1,100)/10)
=MOD(A1,10)

Does that help?

Regards,
Ron

And simpler than mine.
--ron