Numerical Breakdown

[HUBBUB88]

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

 

[Ron Coderre]

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 Rosenfeld]

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

 

[Ron Rosenfeld]

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