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