Excel - Wages Cash Summary

[teebird]

Hi All

I am trying to setup in Excel a spreadsheet that will once I enter the nett wage figure in one column, it will automatically give me a breakup of the number of $100s, $50s, $20s etc - and then I can give a summary to the bank for staff wages.

Net Wage $100 $50 $20 .50c .20c
$450.20 - 4 1 0 0 1

Any assistance much appreciated.
Tee

 

[Lilliabeth]

I hope I understand the question. You had me until you got to the .5 cents and .2 cents.
Perhaps there is a shorter way, I don't know. But this works. I put the net wage in cell A2. I put the denominations across row 1, starting with B1. Then these formulas:
B2: =INT(A2/$B$1)
C2: =INT((A2-($B$1*B2))/$C$1)
D2: =INT((A2-SUM($B$1*B2,$C$1*C2))/$D$1)
E2: =INT((A2-SUM($B$1*B2,$C$1*C2,$D$1*D2))/$E$1)
F2: =INT((A2-SUM($B$1*B2,$C$1*C2,$D$1*D2,$E$1*E2))/$F$1)
G2: =INT((A2-SUM($B$1*B2,$C$1*C2,$D$1*D2,$E$1*E2,$F$1*F2))/$G$1)
H2: =INT((A2-SUM($B$1*B2,$C$1*C2,$D$1*D2,$E$1*E2,$F$1*F2,$G$1*G2))/$H$1)

and so on.

--Lilliabeth

 

[xlhelp]

An alternate solution:

Designate cell A3 as your input cell for the amount payable (450.20)
List the currency units (100, 50, 20, 10, 5, 1, 0.5,0.25, 0.1, 0.05, 0.01) in separate cells in Row 1 (from C1:M1)

In C3 enter the following formula
=TRUNC($A3/C1)
In D3 enter the following formula
=TRUNC(ROUND((($A3-SUM($O$3:O3))/D1),2))
Copy D3 all the way to M3 (D3:M3)
In O3 enter the following formula
=C1*C3
Copy O3 to P3:Y3

In B3 enter the following formula to check the calculation
=SUMPRODUCT((C1:M1)*(C3:M3))

A man has only two choices: He can be right or he can be happy.

 

[xlhelp]

I guess I am fascinated by the subject. One more:

A3 as your input cell for the amount payable (450.20)
List the currency units (100, 50, 20, 10, 5, 1, 0.5, 0.25, 0.1, 0.05, 0.01) in separate cells in Row 1 (from C1:M1)

In C3 enter the following as an array formula
=INT(($A3-SUMPRODUCT(IF(ISNUMBER($A$1:A$1),$A$1:A$1,0),$A3:A3))/C$1)
that is, type the formula and press CTRL/SHIFT/ENTER

copy C3 down to M3.

You might be out by a penny in some cases.



A man has only two choices: He can be right or he can be happy.