Count currency for payment

[books4cars]

I pay employees with cash every week. I have a sheet with a breakdown of the counts of all bills and coins. I have another sheet with the amounts each employee is paid. Is there a way to break down how many of which denominations each one gets? I am using Israeli currency which has the following denominations:
1
2
5
10
20
50
100
200

Thanks

 

[ColinKJ]

Hi books4cars,

Not sure if this sort of thing is what you are after:

<TABLE style="WIDTH: 490pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=653 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=70>Employee</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=71>Week Pay</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>200</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="1020">1,020.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B2/C$1)">5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B2-(C$1*C2))/D$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B2-(C2*C$1)-(D$1*D2))/E$1))">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B2-(C2*C$1)-(D2*D$1)-(E2*E$1))/F$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B2-(C$1*C2)-(D$1*D2)-(E$1*E2)-(F$1*F2))/G$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B2-(C$1*C2)-(D$1*D2)-(E$1*E2)-(F$1*F2)-(G$1*G2))/H$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B2-(C$1*C2)-(D$1*D2)-(E$1*E2)-(F$1*F2)-(G$1*G2)-(H$1*H2))/I$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B2-(C$1*C2)-(D$1*D2)-(E$1*E2)-(F$1*F2)-(G$1*G2)-(H$1*H2)-(I$1*I2))/J$1)">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>958.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B3/C$1)">4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B3-(C$1*C3))/D$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B3-(C3*C$1)-(D$1*D3))/E$1))">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B3-(C3*C$1)-(D3*D$1)-(E3*E$1))/F$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B3-(C$1*C3)-(D$1*D3)-(E$1*E3)-(F$1*F3))/G$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B3-(C$1*C3)-(D$1*D3)-(E$1*E3)-(F$1*F3)-(G$1*G3))/H$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B3-(C$1*C3)-(D$1*D3)-(E$1*E3)-(F$1*F3)-(G$1*G3)-(H$1*H3))/I$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B3-(C$1*C3)-(D$1*D3)-(E$1*E3)-(F$1*F3)-(G$1*G3)-(H$1*H3)-(I$1*I3))/J$1)">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>356.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B4/C$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B4-(C$1*C4))/D$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B4-(C4*C$1)-(D$1*D4))/E$1))">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B4-(C4*C$1)-(D4*D$1)-(E4*E$1))/F$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B4-(C$1*C4)-(D$1*D4)-(E$1*E4)-(F$1*F4))/G$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B4-(C$1*C4)-(D$1*D4)-(E$1*E4)-(F$1*F4)-(G$1*G4))/H$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B4-(C$1*C4)-(D$1*D4)-(E$1*E4)-(F$1*F4)-(G$1*G4)-(H$1*H4))/I$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B4-(C$1*C4)-(D$1*D4)-(E$1*E4)-(F$1*F4)-(G$1*G4)-(H$1*H4)-(I$1*I4))/J$1)">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>231.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B5/C$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B5-(C$1*C5))/D$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B5-(C5*C$1)-(D$1*D5))/E$1))">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B5-(C5*C$1)-(D5*D$1)-(E5*E$1))/F$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B5-(C$1*C5)-(D$1*D5)-(E$1*E5)-(F$1*F5))/G$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B5-(C$1*C5)-(D$1*D5)-(E$1*E5)-(F$1*F5)-(G$1*G5))/H$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B5-(C$1*C5)-(D$1*D5)-(E$1*E5)-(F$1*F5)-(G$1*G5)-(H$1*H5))/I$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B5-(C$1*C5)-(D$1*D5)-(E$1*E5)-(F$1*F5)-(G$1*G5)-(H$1*H5)-(I$1*I5))/J$1)">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>244.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B6/C$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B6-(C$1*C6))/D$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B6-(C6*C$1)-(D$1*D6))/E$1))">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B6-(C6*C$1)-(D6*D$1)-(E6*E$1))/F$1)">2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B6-(C$1*C6)-(D$1*D6)-(E$1*E6)-(F$1*F6))/G$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B6-(C$1*C6)-(D$1*D6)-(E$1*E6)-(F$1*F6)-(G$1*G6))/H$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B6-(C$1*C6)-(D$1*D6)-(E$1*E6)-(F$1*F6)-(G$1*G6)-(H$1*H6))/I$1)">2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B6-(C$1*C6)-(D$1*D6)-(E$1*E6)-(F$1*F6)-(G$1*G6)-(H$1*H6)-(I$1*I6))/J$1)">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>578.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B7/C$1)">2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B7-(C$1*C7))/D$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B7-(C7*C$1)-(D$1*D7))/E$1))">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B7-(C7*C$1)-(D7*D$1)-(E7*E$1))/F$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B7-(C$1*C7)-(D$1*D7)-(E$1*E7)-(F$1*F7))/G$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B7-(C$1*C7)-(D$1*D7)-(E$1*E7)-(F$1*F7)-(G$1*G7))/H$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B7-(C$1*C7)-(D$1*D7)-(E$1*E7)-(F$1*F7)-(G$1*G7)-(H$1*H7))/I$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B7-(C$1*C7)-(D$1*D7)-(E$1*E7)-(F$1*F7)-(G$1*G7)-(H$1*H7)-(I$1*I7))/J$1)">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>123.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B8/C$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B8-(C$1*C8))/D$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B8-(C8*C$1)-(D$1*D8))/E$1))">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B8-(C8*C$1)-(D8*D$1)-(E8*E$1))/F$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B8-(C$1*C8)-(D$1*D8)-(E$1*E8)-(F$1*F8))/G$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B8-(C$1*C8)-(D$1*D8)-(E$1*E8)-(F$1*F8)-(G$1*G8))/H$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B8-(C$1*C8)-(D$1*D8)-(E$1*E8)-(F$1*F8)-(G$1*G8)-(H$1*H8))/I$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B8-(C$1*C8)-(D$1*D8)-(E$1*E8)-(F$1*F8)-(G$1*G8)-(H$1*H8)-(I$1*I8))/J$1)">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>567.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B9/C$1)">2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B9-(C$1*C9))/D$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B9-(C9*C$1)-(D$1*D9))/E$1))">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B9-(C9*C$1)-(D9*D$1)-(E9*E$1))/F$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B9-(C$1*C9)-(D$1*D9)-(E$1*E9)-(F$1*F9))/G$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B9-(C$1*C9)-(D$1*D9)-(E$1*E9)-(F$1*F9)-(G$1*G9))/H$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B9-(C$1*C9)-(D$1*D9)-(E$1*E9)-(F$1*F9)-(G$1*G9)-(H$1*H9))/I$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B9-(C$1*C9)-(D$1*D9)-(E$1*E9)-(F$1*F9)-(G$1*G9)-(H$1*H9)-(I$1*I9))/J$1)">0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Emp_9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>762.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(B10/C$1)">3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B10-(C$1*C10))/D$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT(((B10-(C10*C$1)-(D$1*D10))/E$1))">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B10-(C10*C$1)-(D10*D$1)-(E10*E$1))/F$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B10-(C$1*C10)-(D$1*D10)-(E$1*E10)-(F$1*F10))/G$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B10-(C$1*C10)-(D$1*D10)-(E$1*E10)-(F$1*F10)-(G$1*G10))/H$1)">0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B10-(C$1*C10)-(D$1*D10)-(E$1*E10)-(F$1*F10)-(G$1*G10)-(H$1*H10))/I$1)">1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 align=right x:num x:fmla="=INT((B10-(C$1*C10)-(D$1*D10)-(E$1*E10)-(F$1*F10)-(G$1*G10)-(H$1*H10)-(I$1*I10))/J$1)">0</TD></TR></TBODY></TABLE>

If it is, let me know and I'll post the formulas.

 

[roundstone]

Yes, please. I would need this formula also. Would appreciate your post. Thanks.

 

[RonB1111]

Using the example posted by ColinKJ where the 200 would be in C1,

Enter this formula in C2 and copy down:
=INT(B2/C$1)

Enter this formula in D2 and copy down and across to the end of data"
=INT(($B2-SUMPRODUCT(($C$1:C$1)*($C2:C2)))/D$1)

 

[roundstone]

@RonB1111. Thanks for your fast reply. I got =INT(B2/C$1) right.

But, when I tried the =INT(($B2-SUMPRODUCT(($C$1:C$1)*($C2:C2)))/D$1) array applied, I'm getting some negative numbers.

Although I could look at excel help, may I ask what does the COLON expression do? as in ($C$1:C$1) or ($C2:C2)

May I also ask what does a sumproduct function do? I mean, what applications can it be used for?

My questions are geared so I would understand the formula, not just by way of memorization so I can use it over and over again in similar situations.

Thank you very much for understanding.

 

[RonB1111]

1. Enter the formula with "ENTER" only. Do not use "Ctrl-Shift-Enter". I'm not able to duplicate your negative numbers, so if you still get them after using just the "ENTER" key and following my original instructions, please let me know what amount and what denomination create the negative.

2. SUMPRODUCT is a function where you can multiply a series of cells and sum the total. In D2, it is only multiplying C1*C2. It's written as $C$1:C$1 so that when you copy it into cells on the right, it will expand to $C$1:D$1. So now look in the formula in E2 (after copying it from D2) it's: SUMPRODUCT(($C$1:D$1)*($C2:D2)) which is the sum of C1*C2+D1*D2. I've used SUMPRODUCT in its basic form. Here is a link explaining SUMPRODUCT:
http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[roundstone]

@RonB1111. Got it. Thanks!

The SUMPRODUCT explanation link is most useful.

Need to test it if 'pay amounts' have trailing cents though.

 

[roundstone]

@RonB1111

While I now understand the sumproduct function somewhat already, in the formula below, how is it again that we are dividing

=INT(($B2-SUMPRODUCT(($C$1:C$1)*($C2:C2)))/D$1) ?

If I may, here's my test data and the 'trailing cents' aspects I was referring to earlier:

Employee Week Pay 1000 500 100 50 20 10 5 coins?
Emp_1 3,337.75
Emp_2 478.13
Emp_3 1,082.56
Emp_4 535.25
Emp_5 3,319.06

Thank you for educating me

 

[RonB1111]

For cents, enter the denomination in row 1. I modified the 2nd formula to take care of a rounding issue with cents. Enter this in D2 and copy down and across:
=INT(($B2+0.00000001-SUMPRODUCT(($C$1:C$1)*($C2:C2)))/D$1)

The reason for dividing by D$1 is to calculate the number of each denomination and the INT() portion is to remove fractional amounts.

 

[Nicolaas]

Many thanks for this very useful info!