Separate the amount of money to small unit.

[sakijung]

Good morning. I have a problem about how to separate the amount of money into the small unit like
-------money-3500-2751-2751-3500-4211-
Type-1000------3----2----2-------3-----4---
Type-500-------1----------1-------1---------
Type-100-------2----------7-------------2---
Type-50---------1----------1-----------------
Type-10-----------------------------------1--
Type-5-------------------------------------
Type-1----------------1----1--------------1--
------------------or-----------------------
This help I will apply it for preparing the money for each person in each year.
I have problem about money exchange.
Thank you very much.

 

[crook_101]

Hi,

Is this the sort of thing you are after??

Excel Workbook
	A	B
1		1001
2	1000	1
3	500	0
4	100	0
5	50	0
6	10	0
7	5	0
8	1	1
Sheet3

Cheers,
Ian

 

[sakijung]

All the best,
Thank you very much and Merry Christmas.

 

[sakijung]

I try the number lower than or equal 1000, in the next cell is #value. And I try to key 99, it has 2 times in 20. The others are OK.

 

[Peter_SSs]

Try this formula copied down and across. (I think I got this from a post by pgc01 somewhere in the past)

Excel Workbook
	A	B	C	D
1		3500	2751	4211
2	1000	3	2	4
3	500	1	1	0
4	100	0	2	2
5	50	0	1	0
6	10	0	0	1
7	50	0	0	0
8	1	0	1	11
Split Amounts

 

[sakijung]

Large table removed at OP's request

 

[Peter_SSs]

Is there a further question here?

BTW, when using Excel jeanie, please use the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board. Refer to my screen shot which contains 21 formulas but I only displayed 1 of them.

 

[sakijung]

Start No in B2:L2, and m2 specific the number that you want to separate

=IF(E$1,INT(($M2-SUMPRODUCT($A$1:D$1*$A2:D2))/E$1),0)

It's OK now. Thank you everybody and Merry X'mas...

 

[crook_101]

Hi,

Formula ammend for 1000 value from my original - pleased you are sorted:

Excel Workbook
	A	B
1		980
2	1000	0
3	500	1
4	100	4
5	50	1
6	10	3
7	5	0
8	1	0
Sheet3

Ian,

 

[Peter_SSs]

Start No in B2:L2, and m2 specific the number that you want to separate

=IF(E$1,INT(($M2-SUMPRODUCT($A$1:D$1*$A2:D2))/E$1),0)

It's OK now. Thank you everybody and Merry X'mas...

You appear to have what you want, but would it be possible to see a small amount of sample data and results so we can see just what it was and how this formula was used?