Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
I can't get to you files, but:
at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Hi;
I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Hi George
I set up a table starting in A1 as follows Note Value Takings Retain Value Retain No. Bank Value Bank No. Dollars 1 55 55 55 0 0 Fives 5 45 145 29 80 16 Tens 10 33 0 0 330 33 Twenties 20 50 0 0 1000 50 Fifties 50 15 0 0 750 15 Hundreds 100 3 0 0 300 3 2660 200 84 2460 117 In cell D2 =MIN((200-SUM($D$1:D1)),(C2*B2)) in cell E2 =D2/B2 in cell F2 =C2-D2 in cell G2 =F2/B2 Copy D2:G2 to D3:D7 In cell D8 =SUMPRODUCT((B2:B7)*(C2:C7)) and straight SUM(E2:E7) etc. for the remainder of cells in row 8 Enter number of notes taken in cells C2:C7 and the table shows how many of each note to retain (and values) and how many of each note to bank. -- Regards Roger Govier "George" wrote in message .. . Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Roger, " in cell F2 =C2-D2" should that be B2*C2-D2
-- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Roger Govier" wrote in message ... Hi George I set up a table starting in A1 as follows Note Value Takings Retain Value Retain No. Bank Value Bank No. Dollars 1 55 55 55 0 0 Fives 5 45 145 29 80 16 Tens 10 33 0 0 330 33 Twenties 20 50 0 0 1000 50 Fifties 50 15 0 0 750 15 Hundreds 100 3 0 0 300 3 2660 200 84 2460 117 In cell D2 =MIN((200-SUM($D$1:D1)),(C2*B2)) in cell E2 =D2/B2 in cell F2 =C2-D2 in cell G2 =F2/B2 Copy D2:G2 to D3:D7 In cell D8 =SUMPRODUCT((B2:B7)*(C2:C7)) and straight SUM(E2:E7) etc. for the remainder of cells in row 8 Enter number of notes taken in cells C2:C7 and the table shows how many of each note to retain (and values) and how many of each note to bank. -- Regards Roger Govier "George" wrote in message .. . Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Hi Paul
Quite right. In B3, I had =(B3*C3)-D3 and I should have amended B2 to be the same. Because we are dealing with 1 dollar bills, by chance the formula I posted gives the correct result for row 2, but would not of course for subsequent rows. Well caught!! -- Regards Roger Govier "Paul B" wrote in message ... Roger, " in cell F2 =C2-D2" should that be B2*C2-D2 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Roger Govier" wrote in message ... Hi George I set up a table starting in A1 as follows Note Value Takings Retain Value Retain No. Bank Value Bank No. Dollars 1 55 55 55 0 0 Fives 5 45 145 29 80 16 Tens 10 33 0 0 330 33 Twenties 20 50 0 0 1000 50 Fifties 50 15 0 0 750 15 Hundreds 100 3 0 0 300 3 2660 200 84 2460 117 In cell D2 =MIN((200-SUM($D$1:D1)),(C2*B2)) in cell E2 =D2/B2 in cell F2 =C2-D2 in cell G2 =F2/B2 Copy D2:G2 to D3:D7 In cell D8 =SUMPRODUCT((B2:B7)*(C2:C7)) and straight SUM(E2:E7) etc. for the remainder of cells in row 8 Enter number of notes taken in cells C2:C7 and the table shows how many of each note to retain (and values) and how many of each note to bank. -- Regards Roger Govier "George" wrote in message .. . Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Hi,
Sorry. In original post I made typing error in link, below the proper link http://www.savefile.com/files/58143 Thaks again George. "Gary''s Student" wrote in message ... I can't get to you files, but: at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Hi George
Now that I can see your file, the solution is different to that which I posted, and easier. In cell K21 enter =MIN(E14,INT(($G$27-SUM($M22:$M$31))/I21)) Copy down to cell K28 In cell K29 =INT(($G$27-SUM(M30:M31))/I29) Leave cells K30 and K31 as they are currently with =E23 and =E24 respectively (Incidentally, you don't need all those Plus signs in front of cell values e.g. =+E23, they are a legacy from the days of Lotus 123) -- Regards Roger Govier "George" wrote in message ... Hi, Sorry. In original post I made typing error in link, below the proper link http://www.savefile.com/files/58143 Thaks again George. "Gary''s Student" wrote in message ... I can't get to you files, but: at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Thank you,
I have entered the formula, it works fine. But I have found a small glitch. in some cases next day transfer is a penny more. George. "Roger Govier" wrote in message ... Hi George Now that I can see your file, the solution is different to that which I posted, and easier. In cell K21 enter =MIN(E14,INT(($G$27-SUM($M22:$M$31))/I21)) Copy down to cell K28 In cell K29 =INT(($G$27-SUM(M30:M31))/I29) Leave cells K30 and K31 as they are currently with =E23 and =E24 respectively (Incidentally, you don't need all those Plus signs in front of cell values e.g. =+E23, they are a legacy from the days of Lotus 123) -- Regards Roger Govier "George" wrote in message ... Hi, Sorry. In original post I made typing error in link, below the proper link http://www.savefile.com/files/58143 Thaks again George. "Gary''s Student" wrote in message ... I can't get to you files, but: at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Hi George
Caused by rounding errors in Excel. Change the formula just in cell K21 to =MIN(E14,INT((ROUND($G$27-SUM($M22:$M$31),2))/I21)) -- Regards Roger Govier "George" wrote in message ... Thank you, I have entered the formula, it works fine. But I have found a small glitch. in some cases next day transfer is a penny more. George. "Roger Govier" wrote in message ... Hi George Now that I can see your file, the solution is different to that which I posted, and easier. In cell K21 enter =MIN(E14,INT(($G$27-SUM($M22:$M$31))/I21)) Copy down to cell K28 In cell K29 =INT(($G$27-SUM(M30:M31))/I29) Leave cells K30 and K31 as they are currently with =E23 and =E24 respectively (Incidentally, you don't need all those Plus signs in front of cell values e.g. =+E23, they are a legacy from the days of Lotus 123) -- Regards Roger Govier "George" wrote in message ... Hi, Sorry. In original post I made typing error in link, below the proper link http://www.savefile.com/files/58143 Thaks again George. "Gary''s Student" wrote in message ... I can't get to you files, but: at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Specifically, since Excel (and almost all other computer software) does
binary math (per the IEEE 754 standard), then only .00, .25, .50, and .75 have exact representations; the rest of the decimal fractions must be approximated. When you add these approximate amounts in different a order, then the cumulative effect of the approximations may be different. In particular, removing the largest amounts first will tend to maximize the difference due to these approximations. Since no amount has more than 2 decimal places and you are only adding and subtracting these amounts, rounding results to 2 decimal places eliminates differences due to binary approximations while doing no violence to the calculation. Jerry "Roger Govier" wrote: Hi George Caused by rounding errors in Excel. Change the formula just in cell K21 to =MIN(E14,INT((ROUND($G$27-SUM($M22:$M$31),2))/I21)) -- Regards Roger Govier "George" wrote in message ... Thank you, I have entered the formula, it works fine. But I have found a small glitch. in some cases next day transfer is a penny more. George. "Roger Govier" wrote in message ... Hi George Now that I can see your file, the solution is different to that which I posted, and easier. In cell K21 enter =MIN(E14,INT(($G$27-SUM($M22:$M$31))/I21)) Copy down to cell K28 In cell K29 =INT(($G$27-SUM(M30:M31))/I29) Leave cells K30 and K31 as they are currently with =E23 and =E24 respectively (Incidentally, you don't need all those Plus signs in front of cell values e.g. =+E23, they are a legacy from the days of Lotus 123) -- Regards Roger Govier "George" wrote in message ... Hi, Sorry. In original post I made typing error in link, below the proper link http://www.savefile.com/files/58143 Thaks again George. "Gary''s Student" wrote in message ... I can't get to you files, but: at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Thank you,
I have entered the formula, it works fine. But I have found a small glitch. in some cases next day transfer is a penny more. George. "Roger Govier" wrote in message ... Hi George Now that I can see your file, the solution is different to that which I posted, and easier. In cell K21 enter =MIN(E14,INT(($G$27-SUM($M22:$M$31))/I21)) Copy down to cell K28 In cell K29 =INT(($G$27-SUM(M30:M31))/I29) Leave cells K30 and K31 as they are currently with =E23 and =E24 respectively (Incidentally, you don't need all those Plus signs in front of cell values e.g. =+E23, they are a legacy from the days of Lotus 123) -- Regards Roger Govier "George" wrote in message ... Hi, Sorry. In original post I made typing error in link, below the proper link http://www.savefile.com/files/58143 Thaks again George. "Gary''s Student" wrote in message ... I can't get to you files, but: at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need formula: too complexe for me..
Hi George
It's the same problem with rounding, as before, and explained very well by Jerry as being due to the fact that decimal fractions have to be approximated. Amend the relevant formula to =Round(the_Formula,2) -- Regards Roger Govier "George" wrote in message .. . Thank you, I have entered the formula, it works fine. But I have found a small glitch. in some cases next day transfer is a penny more. George. "Roger Govier" wrote in message ... Hi George Now that I can see your file, the solution is different to that which I posted, and easier. In cell K21 enter =MIN(E14,INT(($G$27-SUM($M22:$M$31))/I21)) Copy down to cell K28 In cell K29 =INT(($G$27-SUM(M30:M31))/I29) Leave cells K30 and K31 as they are currently with =E23 and =E24 respectively (Incidentally, you don't need all those Plus signs in front of cell values e.g. =+E23, they are a legacy from the days of Lotus 123) -- Regards Roger Govier "George" wrote in message ... Hi, Sorry. In original post I made typing error in link, below the proper link http://www.savefile.com/files/58143 Thaks again George. "Gary''s Student" wrote in message ... I can't get to you files, but: at the end of the day put the number of hundreds in A1 the number of fifties in A2 the number of twenties in A3 the number of tens in A4 the number of fives in A5 the number of ones in A6 then run this macro: Sub gsnu() talley = 0 hundreds = Range("A1").Value fifties = Range("A2").Value twenties = Range("A3").Value tens = Range("A4").Value fives = Range("A5").Value ones = Range("A6").Value p1 = 0 p5 = 0 p10 = 0 p20 = 0 p50 = 0 p100 = 0 total = 100 * hundreds + 50 * fifties + 20 * twenties total = total + 10 * tens + 5 * fives + ones MsgBox (total) For i = 1 To ones talley = talley + 1 p1 = p1 + 1 If talley = 200 Then GoTo done Next For i = 1 To fives talley = talley + 5 p5 = p5 + 1 If talley = 200 Then GoTo done Next For i = 1 To tens talley = talley + 10 p10 = p10 + 1 If talley = 200 Then GoTo done Next For i = 1 To twenties talley = talley + 20 p20 = p20 + 1 If talley = 200 Then GoTo done Next For i = 1 To fifties talley = talley + 50 p50 = p50 + 1 If talley = 200 Then GoTo done Next For i = 1 To hundreds talley = talley + 100 p100 = p100 + 1 Next done: MsgBox ("Put" & Chr(10) & p1 & " ones" & Chr(10) & p5 & " fives" & Chr(10) & p10 & " tens" & Chr(10) & p20 & " twenties" & Chr(10) & p50 & " fifties" & Chr(10) & p100 & " hundreds" & Chr(10) & " back in register") deposit = total - p100 * 100 - p50 * 50 - p20 * 20 - p10 * 10 - p5 * 5 - p1 MsgBox ("deposit " & deposit) End Sub It will tell you what bills to return to the register and what amount to deposit -- Gary's Student "George" wrote: Hi; I am working in a company that we use cash balancing sheet at the end of the day in theory there is 200$ at morning, at the end of the day deposit all the balance - 200$ for the nex day. rules are to take out the biggest cash bills in first. I have saved files in http://www.savefile/files/58143 thank you George.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |