separate in hundreds, thousands, etc

How to separate - let say - 278.950 (in cell A1) become :

(B1) 2 pcs of 100.000
(C1) 1 pc of 50.000
(D1) 1 pc of 20.000
(E1) 1 pc of 5.000
(F1) 3 pcs of 1.000
(G1) 1 pc of 500
(H1) 4 pcs of 100
(I1) 1 pc of 50

I have hundreds more in colomn A (down)
Finally when I sum colomn down I have .... pcs of 100.000, 50.000,
20.000,,,, etc

I hope someone will understand to what I mean

thx

Enter 278950 in cell A2
B1: 100000
D1: 50000
F1: 20000.... and so on for all denominations (leaving a column gap)

B2: =INT(A2/B1)
C2: =MOD(A2,B1)

Select cells B2:C2
and copy across under all columns as follows
D2:E2, F2:G2 and so on

Then you can copy down.

Mangesh



"!..:: Enang ::..!" <[email protected]> wrote in message
news:[email protected]...
> How to separate - let say - 278.950 (in cell A1) become :
>
> (B1) 2 pcs of 100.000
> (C1) 1 pc of 50.000
> (D1) 1 pc of 20.000
> (E1) 1 pc of 5.000
> (F1) 3 pcs of 1.000
> (G1) 1 pc of 500
> (H1) 4 pcs of 100
> (I1) 1 pc of 50
>
> I have hundreds more in colomn A (down)
> Finally when I sum colomn down I have .... pcs of 100.000, 50.000,
> 20.000,,,, etc
>
> I hope someone will understand to what I mean
>
> thx
>
>
>

A small mistake in absolute referencing:
In B2 use: =INT(A2/B$1)
In C2: =MOD(A2,B$1)

Mangesh




"Mangesh Yadav" <[email protected]> wrote in message
news:#[email protected]...
> Enter 278950 in cell A2
> B1: 100000
> D1: 50000
> F1: 20000.... and so on for all denominations (leaving a column gap)
>
> B2: =INT(A2/B1)
> C2: =MOD(A2,B1)
>
> Select cells B2:C2
> and copy across under all columns as follows
> D2:E2, F2:G2 and so on
>
> Then you can copy down.
>
> Mangesh
>
>
>
> "!..:: Enang ::..!" <[email protected]> wrote in message
> news:[email protected]...
> > How to separate - let say - 278.950 (in cell A1) become :
> >
> > (B1) 2 pcs of 100.000
> > (C1) 1 pc of 50.000
> > (D1) 1 pc of 20.000
> > (E1) 1 pc of 5.000
> > (F1) 3 pcs of 1.000
> > (G1) 1 pc of 500
> > (H1) 4 pcs of 100
> > (I1) 1 pc of 50
> >
> > I have hundreds more in colomn A (down)
> > Finally when I sum colomn down I have .... pcs of 100.000, 50.000,
> > 20.000,,,, etc
> >
> > I hope someone will understand to what I mean
> >
> > thx
> >
> >
> >
>
>

And a still better approach:

A2: 278950

B1:I1
100000 50000 20000 5000 1000 500 100 50


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

Copy across under all columns till I.

You can then copy down

Mangesh




"Mangesh Yadav" <[email protected]> wrote in message
news:#[email protected]...
> Enter 278950 in cell A2
> B1: 100000
> D1: 50000
> F1: 20000.... and so on for all denominations (leaving a column gap)
>
> B2: =INT(A2/B1)
> C2: =MOD(A2,B1)
>
> Select cells B2:C2
> and copy across under all columns as follows
> D2:E2, F2:G2 and so on
>
> Then you can copy down.
>
> Mangesh
>
>
>
> "!..:: Enang ::..!" <[email protected]> wrote in message
> news:[email protected]...
> > How to separate - let say - 278.950 (in cell A1) become :
> >
> > (B1) 2 pcs of 100.000
> > (C1) 1 pc of 50.000
> > (D1) 1 pc of 20.000
> > (E1) 1 pc of 5.000
> > (F1) 3 pcs of 1.000
> > (G1) 1 pc of 500
> > (H1) 4 pcs of 100
> > (I1) 1 pc of 50
> >
> > I have hundreds more in colomn A (down)
> > Finally when I sum colomn down I have .... pcs of 100.000, 50.000,
> > 20.000,,,, etc
> >
> > I hope someone will understand to what I mean
> >
> > thx
> >
> >
> >
>
>

Chip Pearson wrote a user defined function that may help you:

http://groups.google.co.uk/group/mic...36b2fbc18e86a2
or

http://snipurl.com/hw3l

There is a typo in his code, though. And you want to include tenths of cents.

Change this line:
Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.5, 0.01)
to
Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01, 0.001)

Chip has this in the instructions:
To use it in a worksheet, select a range of 10 cells, e.g., D2:M2, type
=ConvertToCurrency(A2) and press Ctrl+Shift+Enter.

You'll want to select 11 cells (D2:N2) to include that tenth of cent.


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

"!..:: Enang ::..!" wrote:
>
> How to separate - let say - 278.950 (in cell A1) become :
>
> (B1) 2 pcs of 100.000
> (C1) 1 pc of 50.000
> (D1) 1 pc of 20.000
> (E1) 1 pc of 5.000
> (F1) 3 pcs of 1.000
> (G1) 1 pc of 500
> (H1) 4 pcs of 100
> (I1) 1 pc of 50
>
> I have hundreds more in colomn A (down)
> Finally when I sum colomn down I have .... pcs of 100.000, 50.000,
> 20.000,,,, etc
>
> I hope someone will understand to what I mean
>
> thx

--

Dave Peterson

Perfect!!! and THANK YOU very much!!!

This's my formula before..... LOL

=IF($I6<>"",ROUNDDOWN($I6/N$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5))/O$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5))/P$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5)-($P6*P$5))/Q$5,0),0)
=
IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5)-($P6*P$5)-($Q6*Q$5))/R$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5)-($P6*P$5)-($Q6*Q$5)-($R6*R$5)
)/S$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5)-($P6*P$5)-($Q6*Q$5)-($R6*R$5)
-($S6*S$5))/T$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5)-($P6*P$5)-($Q6*Q$5)-($R6*R$5)
-($S6*S$5)-($T6*T$5))/U$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5)-($P6*P$5)-($Q6*Q$5)-($R6*R$5)
-($S6*S$5)-($T6*T$5)-($U6*U$5))/V$5,0),0)
=IF($I6<>"",ROUNDDOWN(($I6-($N6*N$5)-($O6*O$5)-($P6*P$5)-($Q6*Q$5)-($R6*R$5)
-($S6*S$5)-($T6*T$5)-($U6*U$5)-($V6*V$5))/W$5,0),0)


But it show circular mark on the status bar without pointing to a cell




"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> And a still better approach:
>
> A2: 278950
>
> B1:I1
> 100000 50000 20000 5000 1000 500 100 50
>
>
> B2: =INT($A2/B$1)
> C2: =INT(($A2-SUMPRODUCT($B$1:B$1,$B2:B2))/C$1)
>
> Copy across under all columns till I.
>
> You can then copy down
>
> Mangesh
>
>
>
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:#[email protected]...
> > Enter 278950 in cell A2
> > B1: 100000
> > D1: 50000
> > F1: 20000.... and so on for all denominations (leaving a column gap)
> >
> > B2: =INT(A2/B1)
> > C2: =MOD(A2,B1)
> >
> > Select cells B2:C2
> > and copy across under all columns as follows
> > D2:E2, F2:G2 and so on
> >
> > Then you can copy down.
> >
> > Mangesh
> >
> >
> >
> > "!..:: Enang ::..!" <[email protected]> wrote in message
> > news:[email protected]...
> > > How to separate - let say - 278.950 (in cell A1) become :
> > >
> > > (B1) 2 pcs of 100.000
> > > (C1) 1 pc of 50.000
> > > (D1) 1 pc of 20.000
> > > (E1) 1 pc of 5.000
> > > (F1) 3 pcs of 1.000
> > > (G1) 1 pc of 500
> > > (H1) 4 pcs of 100
> > > (I1) 1 pc of 50
> > >
> > > I have hundreds more in colomn A (down)
> > > Finally when I sum colomn down I have .... pcs of 100.000, 50.000,
> > > 20.000,,,, etc
> > >
> > > I hope someone will understand to what I mean
> > >
> > > thx
> > >
> > >
> > >
> >
> >
>
>