separate in hundreds, thousands, etc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

!..:: Enang ::..! - 23 Sep 2005 10:21 GMT

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

Reply to this Message

Mangesh Yadav - 23 Sep 2005 11:14 GMT

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

> How to separate - let say - 278.950 (in cell A1) become :
>
[quoted text clipped - 14 lines]
>
> thx

Reply to this Message

Mangesh Yadav - 23 Sep 2005 11:26 GMT

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

Mangesh

> Enter 278950 in cell A2
> B1: 100000
[quoted text clipped - 30 lines]
> >
> > thx

Reply to this Message

Mangesh Yadav - 23 Sep 2005 11:39 GMT

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

> Enter 278950 in cell A2
> B1: 100000
[quoted text clipped - 30 lines]
> >
> > thx

Reply to this Message

!..:: Enang ::..! - 24 Sep 2005 02:42 GMT

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

> And a still better approach:
>
[quoted text clipped - 46 lines]
> > >
> > > thx

Reply to this Message

Dave Peterson - 23 Sep 2005 13:04 GMT

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

http://groups.google.co.uk/group/microsoft.public.excel.links/browse_frm/thread/
8fedae5dfc843ddb/2f36b2fbc18e86a2?lnk=st&q=group:*excel*+insubject:%22Money+insu
bject:conversion+insubject:formula%22+author:pearson&rnum=1&hl=en#2f36b2fbc18e86
a2
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 :
>
[quoted text clipped - 14 lines]
>
> thx

Signature

Dave Peterson

Reply to this Message