Need formula: too complexe for me..

[Gary''s Student]

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

 

[George]

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..

 

[Roger Govier]

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$11)),(C2*B2))
in cell E2
=D2/B2
in cell F2
=C2-D2
in cell G2
=F2/B2
Copy D2:G2 to D37

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.

 

[Paul B]

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

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$11)),(C2*B2))
in cell E2
=D2/B2
in cell F2
=C2-D2
in cell G2
=F2/B2
Copy D2:G2 to D37

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.

 

[Roger Govier]

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


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

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$11)),(C2*B2))
in cell E2
=D2/B2
in cell F2
=C2-D2
in cell G2
=F2/B2
Copy D2:G2 to D37

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.

 

[George]

Hi,
Sorry. In original post I made typing error in link, below the proper link
http://www.savefile.com/files/58143
Thaks again
George.

 

[Roger Govier]

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)

 

[George]

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]

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))

 

[Jerry W. Lewis]

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

 

[George]

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]

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)