Summaries Amount of money

[Faio]

Anyone knows a better way to make a summary of a Total Amount, say

$345.00 into $100, $50.00, $20.00, $10.00, $5.00, $2.00, $1.00, 0.50, 0.20,
0.10, 0.05

or just into $50.00, $20.00, $10.00, $5.00, $2.00, $1.00, 0.50, 0.20, 0.10,
0.05 when I ran out of $100.00?

I have a table which calculate pay for employees such as
============================================================================
=========

Total Pay Summary
Name, Hourly rate, Days worked, Total Amount, $100, $50.00, $20.00, $10.00,
$5.00, $2.00, $1.00, 0.50, 0.20, 0.10, 0.05
----------------------------------------------------------------------------
----------------------------------------------------
John, $5.65, 7, $38.92,


Note that the commas represent coulmns.

Anyone can help on this?

Thanks in advance.

 

[JulieD]

Hi Faio

not sure it's the best way here's one way:
in cells E1 to O1 enter your currency ($100,$50....0.05)
assuming D2 has the amount to split up
use the following formulas:
E2 =INT(D2/E1)
F2 =INT(($D$2-(E2*E1))/F1)
G2 =INT(($D$2-SUM(E2*E1,F2*F1))/G1)
H2 =INT(($D$2-SUM(E2*E1,F2*F1,G2*G1))/H1)
I2 =INT(($D$2-SUM(E2*E1,F2*F1,G2*G1,H2*H1))/I1)
carry on with the pattern up to L2
O2
=INT(($D$2-SUM(E2*E1,F2*F1,G2*G1,H2*H1,I2*I1,J2*J1,K2*K1,L2*L1,M2*M1,N2*N1))
/O1)

The only way to deal with running out of $100 is to manually change the
value in E2 to 0 (this will, of course, delete the formula).

Hope this helps
Cheers
JulieD

 

[Leo Heuser]

Hi Faio

not sure it's the best way here's one way:
in cells E1 to O1 enter your currency ($100,$50....0.05)
assuming D2 has the amount to split up
use the following formulas:
E2 =INT(D2/E1)
F2 =INT(($D$2-(E2*E1))/F1)
G2 =INT(($D$2-SUM(E2*E1,F2*F1))/G1)
H2 =INT(($D$2-SUM(E2*E1,F2*F1,G2*G1))/H1)
I2 =INT(($D$2-SUM(E2*E1,F2*F1,G2*G1,H2*H1))/I1)
carry on with the pattern up to L2
O2
=INT(($D$2-SUM(E2*E1,F2*F1,G2*G1,H2*H1,I2*I1,J2*J1,K2*K1,L2*L1,M2*M1,N2*N1))
/O1)

The only way to deal with running out of $100 is to manually change the
value in E2 to 0 (this will, of course, delete the formula).

Hope this helps
Cheers
JulieD

Or to make it easier to enter:
(Please notice the use of mixed references ($ and no $))

Amounts in D2 and down.

E2 =INT(D2/$E$1)
F2 =INT(($D2-SUMPRODUCT($E$1:E$1,$E2:E2)+0.009)/F$1)

Copy F2 to O2 with the fill handle (the little square in the lower
right corner of the cell).
Select E2:O2 and copy down with the fill handle.

 

[JulieD]

Hi Leo

much neater solution .. i knew there had to be a SUMPRODUCT in there
somehow, just couldn't figure it!

Cheers
JulieD

 

[Leo Heuser]

Hi Leo

much neater solution .. i knew there had to be a SUMPRODUCT in there
somehow, just couldn't figure it!

Cheers
JulieD

Thanks, Julie
Yes SUMPRODUCT is a very versatile function.

 

[Faio]

Thanks U 2.

It helps me alot.

Thanks, Julie
Yes SUMPRODUCT is a very versatile function.