using excel formulas for coin analysis

[rushdhi]

We pay medical reimbursements to to our employees by CASH and for this I need to figure out how many notes / coins of each currency denomination I need to get from the bank. Our Currency denominations come in
1000, 500, 200, 100,50,20 and 10 Notes and we have coins in 5,2,1 ,.50 and .25

Eg:
Mr. X Amount 2575.75 - that would be 2 x 1000, 1 x 500, 1 x 50 , 1 x 20 and 1 x 5 (Coin)

Thanks
Rushdhi

 

[BigC]

Rushdi
In a blank sheet, designate cell D1 as your input cell for the amount payable (2,575.75)
List the currency units (1000,500,...) in separate cells in one column (say from A3:A14)
Use B3:B14 as a Y/N flag in case a particular denomination is not available at any time
In C3 enter the following formula
=If(B3="Y",TRUNC($D$1/A3),0)
In C4 enter the following formula
=If(B4="Y",TRUNC(($D$1-SUM($D$3:D3))/A4),0)
Copy C4 to C5:C14
In D3 enter the following formula
=+A3*C3
Copy D3 to D4:D14
In D15 enter the following formula to sum the results
=SUM(D3:D14)
In D16 enter the following formula to check the calculation
=+D1-D15

HTH
BigC
This message was edited by BigC on 2002-10-16 03:25

 

[Dai Makka]

ah, not bag im impressed but i need it to round up to whole quantities.

say i have 3.6, 2.4 & 1.2m lengths and i need to divide 7.4m - that would equal 1 x 3.6m, 1 x 2.4m and 2 x 1.2m lengths

any thoughts please

 

[Neotramm]

New to this forum and learning Excel. I collect quarters, dimes, nickels, and pennies in a big jar. When I get a lot of change in the jar, I stack the coins into the appropriate one of the appropriate stacks. I then put the appropriate coins into paper rolls (a roll of quarters is $10, a roll of dimes is $5, a roll of pickles is $2 and a roll of pennies is $.50). I then add the rolls to my box of rolled coins. I want to track the rolls, amounts, and the dates that I add them to the box of rolled coins.

Any suggestions?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
2	Date		0.25	0.1	0.05	0.01
3		Total	10.00	5.00	2.00	0.50	0.25	0.10	0.05	0.01
4	6-Apr-19	45.35	4	1	0	0	1	1	0	0
5
1bbb

Cell Formulas
Range	Formula
A4	=TODAY()
B4	=SUMPRODUCT(C3:J3,C4:J4)

You could have started a new thread with your question.
Hopefully the above helps.