rounding numbers
Snowman
rounded off to 2 digits. I format the cell correct but
when I add a bunch of them together the total is usually
a penny or 2 off because it is adding more than just the
2 digits past the decimal point. I have tried
the "integer" function and the "rounding" function but
neither work.
Laura Cook
Example:
=10.37*56%
-will equal 5.8072
=ROUND(10.37*56%,2)
-will equal 5.81
--
HTH,
Laura Cook
Appleton, WI
"Snowman" <catr...@charter.net> wrote in message
news:02b601c2ddd1$cd11bbb0$a201...@phx.gbl...
J.E. McGimpsey
A1: 1.00
A2: =ROUND(A1/3,2) ==> 0.33
A3: =ROUND(A1/3,2) ==> 0.33
A4: =ROUND(A1/3,2) ==> 0.33
A5: =SUM(A2:A4) ==> 0.99
One way to handle this is to use
A4: =A1 - SUM(A2:A3) ==> 0.34
but it means that you have to decide which "bin" gets the extra or
short bit, and, if you had 13 of these, say, then
A1 1
A2 =ROUND(A1/13,2) ==> 0.08
A3 =ROUND(A1/13,2) ==> 0.08
...
A13 =ROUND(A1/13,2) ==> 0.08
A14 =A1 - SUM(A2:A13) ==> 0.04 !
A15 =sum(A2:A14) ==> 1
and your distribution becomes rather ununiform. Especially in financial
type transactions, you need to plan for where the corrections occur,
and it's not always the obvious place.
In article <#vPNjRd3...@TK2MSFTNGP12.phx.gbl>, Laura Cook
Jerry W. Lewis
Formatting toolbar only affects the display -- and does not actually
round the underlying value.
Laura has discussed the use of the ROUND function. Alternately you
could check "Precision as displayed" under Tools|Options|Calculation.
Then the sum will agree with manual adding of the rounded values, but as
J.E. McGimpsey pointed out, that sum still may not be exactly what you
expect.
Jerry
David J. Braden
Would so-called "banker's rounding" obviate this?
TIA
Dave Braden
In article <260220031815084888%jemcg...@mvps.org>,
"J.E. McGimpsey" <jemcg...@mvps.org> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
J.E. McGimpsey
groups. I'd been using it since HS, since every experimental science
dealing with continuous data uses the technique to eliminate bias.
"Banker's rounding" is the same a XL's ROUND() function except when the
least significant digit + 1 = 5. Banker's rounding rounds such numbers
to the nearest even digit, while XL always rounds *up*:
Value XL BR
0.412 0.41 0.41
0.418 0.42 0.42
0.415 0.42 0.42
0.425 0.43 0.42
So in this case, no, it wouldn't help since for both BR and XL
ROUND(1/3,2) = 0.33
Note that VBA 5.3+ uses "banker's rounding".
In article <see_sig-6F04F0...@msnews.microsoft.com>, David
Jerry W. Lewis
Excel ROUND) or if you use ASTM/IEEE rounding ("Banker's Rounding" per
VBA ROUND)
Do bankers use "Banker's Rounding"? I have been unable to find any
evidence of it. As far as I can tell, the term was coined by
programmers relatively recently.
Jerry
Tushar Mehta
with an email copy to Snowman.
Please use the newsgroup for further discussion.]
Rounding and summing the rounded values will never guarantee the
expected result. However, there are steps one can take to mitigate the
problem.
You've already received some suggestions on how to do so. Here's a
summary of six techniques that I am aware of. None of the techniques
is completely satisfying but that is the nature of the underlying
limitations of digital computations (and not just XL).
(1) Do nothing. The results are not pleasing to the eye but are
accurate -- or as accurate as any digital computer can produce
(2) Format the result to 2 decimals but leave the underlying
calculations untouched. The final SUM will be accurate but manual
calculations of the displayed individual values will not match the SUM.
(3) Use XL's 'Precision as displayed' option. XL will match what one
would get by hand, but the results may not be as desired. [Jerry Lewis
mentioned this in his post in this discussion.]
(4) Correct the difference between the sum of the individual items and
the expected final value by allocating the difference to the last item
in the list. This is popular because it is easy to implement.
However, the problem here is that the last item might be a small number
and the percentage effect on it might be unacceptable.
(5) Correct the difference between the sum of the individual items and
the expected final value by allocating the difference to the largest
item. This alleviates the percent impact on the item of the
correction. However, what if there is no clearly defined largest item?
Also, this is hard to programmatically implement (though, in XL it is
somewhat easier with iterative calculations).
[J.E.'s example of allocating 1 into 13 items demonstrates the problems
with both (4) and (5)]
(6) While I am sure this isn't a method, I haven't seen it discussed in
the XL NGs. I tend to use this when the relative sizes of the
individual items are more or less the same. It spreads the pain across
multiple items, is easy to implement, and adapts to changes.
The basic idea is to reassess the problem after each allocation. Find
the new 'total amount to be distributed' and the new 'total number of
items remaining.' Now, distribute this new amount among the remaining
items. An example, using XL, would be the following. Suppose G1
contains the amount (say, 1) to be distributed among, say, 13 items,
each of which will be in G2:G14.
Then, the formula in G2 will be =ROUND(G1/13,2)
The formula in G3, however, will distribute the remaining amount (G1-
G2) among 12 items. The formula in G4 will distribute the amount G1-
G2-G3 among 11 items. And, so on. The formula in G3 is
=ROUND(($G$1-SUM($G$2:G2))/(13-(ROW()-2)),2)
Copy G3 all the way down to G14 and the distribution of 1 in G1
becomes:
0.08
0.08
0.08
0.08
0.08
0.08
0.07
0.08
0.07
0.08
0.07
0.08
0.07
It is still not perfect in that the correction kicks in only after a
cumulative error reaches a certain 'threshold.' This means that the
first so-many items will usually not be subject to any potential
correction.
--
Trouble finding replies to your posts? Use a newsreader. See the
tutorial 'Outlook Express and Newsgroups' on my web site
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP - Excel
In article <02b601c2ddd1$cd11bbb0$a201...@phx.gbl>,
catr...@charter.net says...
Tushar Mehta
should read "While I am sure this isn't a new method, ..."
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <MPG.18c938614...@msnews.microsoft.com>,
ng_p...@bigfoot.com says...
David J. Braden
I skimmed the thread too quickly. My flickering candle of brilliant
insight was misplaced. Sorry.
Etymology of "banker's rounding"? No idea. Seems like a nice idea. If
the term were coined by programmers, I'm surprised the bankers got off
with no derision. <g> Evidently it is the default approach taken in
some other packages; Delphi and Python?
The only call for it I have seen was indeed by practicioners in
financial services. Peter Beach, who has years of experience in this
stuff, might better inform us.
Whether or not it *is* used in the industry, though, doesn't concern me.
The concept is an excellent one, IMHO.
Regards,
Dave B
In article <3E5ECE8F.6010104@no_e-mail.com>,
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
J.E. McGimpsey
anywhere that you need to avoid a bias away from zero.
In article <see_sig-6A9D7E...@msnews.microsoft.com>, David
Harlan Grove
..
>Etymology of "banker's rounding"? No idea. Seems like a nice idea. If
>the term were coined by programmers, I'm surprised the bankers got off
>with no derision. <g> Evidently it is the default approach taken in
>some other packages; Delphi and Python?
True for Delphi, not for Python. From the Python manual:
"round(x[, n])
Return the floating point value x rounded to n digits after the decimal point.
If n is omitted, it defaults to zero. The result is a floating point number.
Values are rounded to the closest multiple of 10 to the power minus n; if two
multiples are equally close, rounding is done away from 0 (so. for example,
round(0.5) is 1.0 and round(-0.5) is -1.0)."
AFAIK, Delphi is the only implementation of any general purpose programming
language that provides bankers' rounding. Rounding away from zero is the rule.
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
J.E. McGimpsey
looking at it if it limits the variation to one digit in the last
place. Guess I'll try a few scenarios...
In article <MPG.18c938614...@msnews.microsoft.com>, Tushar
David J. Braden
Harlan Grove<hrl...@aol.com> wrote:
> "David J. Braden" wrote...
> ..
> >Etymology of "banker's rounding"? No idea. Seems like a nice idea. If
> >the term were coined by programmers, I'm surprised the bankers got off
> >with no derision. <g> Evidently it is the default approach taken in
> >some other packages; Delphi and Python?
> ..
>
> True for Delphi, not for Python. From the Python manual:
>
> "round(x[, n])
>
Yet there is Python's default algorithm used for FixedPoint:
http://fixedpoint.sourceforge.net/html/lib/fixedpoint-rounding.html
I don't know the language, though it looks *mighty* interesting.
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
David J. Braden
unnatural sciences as well.
Interesting: given what I suspect is Excel's primary and target user
base, I wonder if requesting ROUND adopt the BR convention wouldb't be a
reasonable request in future versions.
I brace myself for arguments against the idea.
Dave B
In article <280220031158443914%jemcg...@mvps.org>,
"J.E. McGimpsey" <jemcg...@mvps.org> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Tushar Mehta
introduce a bias away from zero? And, why would anyone use "banker's
rounding?" Other than a bank when calculating interest owed its
customers?
Consider numbers spanning from some odd integer +0.5 to +2.5 to figure
out why BR introduces a subtle *downward* bias. For simplicity, and
without any loss of generality, consider 1.5 <= x < 3.5 and consider
rounding to integers.
With 'normal' rounding:
1.5 <= x < 2.5 : round to 2
2.5 <= x < 3.5 : round to 3
With 'bankers' rounding:
1.5 <= x <= 2.5 : round to 2
2.5 < x < 3.5 : round to 3
Normal rounding is perfectly symmetrical is what range of numbers get
mapped on to what integer. "Banker's rounding" has (a wee bit) more
'mass' at the lower number.
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <280220031158443914%jemcg...@mvps.org>,
jemcg...@mvps.org says...
J.E. McGimpsey
Number XL Round BR
1.5 2 2
2 2 2
2.5 3 2
3 3 3
3.5 4 4
4 4 4
4.5 5 4
5 5 5
5.5 6 6
6 6 6
6.5 7 6
7 7 7
Averages:
4.25 4.5 4.25
or
Number XL Round BR
2.5 3 2
1.5 2 2
3.5 4 4
Averages:
2.5 3 2.666666667
or
Number XL Round BR
2.5 3 2
3.5 4 4
4.5 5 4
Averages:
3.5 4 3.333333333
IIRC, for any distribution in which the 0.5 is equally likely to fall
between all integer pairs (e,g., 0.5, 1.5, 2.5, 3.5 vs. 0.5, 2.5, 4.5,
6.5), "BR" is no worse and often a better method of ensuring that the
measure of central tendency is not biased away from 0.
In article <MPG.18ca76852...@msnews.microsoft.com>, Tushar
Jerry W. Lewis
since the early 1940's though not with the name "banker's rounding"). I
have a statistical reference from the 1940's that presumes it to
standard practice in data recording. I have annecdotal evidence (can
anyone supply an actual reference?) that it was recommended by the
American Mathematical Society around 1900. But that is the extent of my
knowledge on the origin of the concept.
I don't have a strong enough financial background to evaluate the term
"banker's rounding", but I don't know any finance people who have ever
heard of it, and web pages dealing with Euro conversions explicitly
specify rounding 5's up (as does the IRS).
The binary equivalent of the so-called "banker's rounding" is an IEEE
standard, which was adopted by Intel in designing the Pentium chip,
which may in part account for why many software languages use it.
Jerry W. Lewis
difinitive name?) and the so-called "banker's rounding" round to the
nearest number of the desired precision. The only difference is in the
case of ties. Your familiar rounding always rounds ties up -- hence the
bias. So-called "banker's rounding" rounds half of the possible ties up
and the other half down, thus it is often referred to as "unbiased rounding"
Unbiased rounding has been the ASTM standard for over 60 years. The
binary equivalent is an IEEE standard, where it plays an import role in
reducing accumulated rounding errors in chain calculations.
Jerry
Tushar Mehta
in this discussion (or in any discussion in the past) makes sense to
me.
I just don't see the bias in regular rounding and I see nothing but a
bias in banker's rounding.
Nor do I understand the concept of a 'tie.' At least to me, the rules
for rounding are quite definitive.
Consider the range of numbers 1.5 <= x < 3.5. Measure numbers in this
range at any desired degree of precision. I'll consider two examples.
First, measure them at a single decimal value. We get 20 possible
numbers 1.5, 1.6, ..., 2.0, ... 2.5, ... 3.0, ... 3.4
With banker's rounding 11 of these 20 numbers map to 2; 9 to 3.
With conventional rounding, 10 numbers map onto 2 and to 3.
Refine it further to 2 decimal places. There are 200 numbers from 1.50
to 3.49. With banker's rounding 101 map to 2 and 99 map to 3. The
other way 100 map to each integer.
I suspect the 'rationalization' for bankers rounding comes from some
amount of handwaving. Focus only on those numbers that need changing
(i.e., remove all numbers ending in zero from the analysis) and look at
all numbers at some level of precision, in the range x.0 < y < (x+1).0
Now, one can argue, rather simplisitically, IMO, that of the numbers
that get changed more get moved up (5 at 1 decimal place, 50 at 2
decimal places) than down (4 or 49 respectively).
But the effect of this kind of analysis on the *original* set of
numbers (that included numbers ending in zero) is very biased.
Unless there is evidence to the contrary, I see no reason to disbelieve
the possibility that some banker came up with this method to calculate
interest that should be paid to customers.
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <3E60DE88.6070807@no_e-mail.com>, post_a_reply@no_e-mail.com
says...
J.E. McGimpsey
Try a slight variation on your example, focusing on the delta between
the original value and the mapped value:
Consider the range of numbers 1.5 <= x < 3.5, measured at a single
decimal value to get 20 possible numbers, 1.5...3.4.
Using XL's rounding, 10 of those numbers round up, 8 round down and 2
are static.
Using BR, 9 of those numbers round up, 9 round down and 2 are static.
Using XL's rounding, the total delta for numbers rounded up is +3 while
the total delta for numbers rounded down is -2.0 for a net bias of
+1.0, or an average bias of +0.05 per data point.
Using BR rounding, the total delta for numbers rounded up is +2.5 while
the total delta for numbers rounded down is -2.5 for a net bias of 0.0,
or an average bias of +0.00 per data point.
The bias in XL rounding is definitely away from 0.
In article <MPG.18cac0786...@msnews.microsoft.com>, Tushar
David J. Braden
Do you see it yet?
No handwaving, believe me. Do you honestly think that John, Jerry or I
would do that?!? I wish I coud point you to the thread where this was
first made clear to me; I don't recall it. It included Stephen B, anong
others. A few data sets, including that of the OP, hammered it home for
me.
Regards,
Dave B
In article <MPG.18cac0786...@msnews.microsoft.com>,
Tushar Mehta <ng_p...@bigfoot.com> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
David J. Braden
Many?!? I would hope so, and will push that MS Excel adopts it, but what
languages, other then Delphi and (in some cases) Python, adopt this
sendible approach?
TIA
Dave B
In article <3E60DCFD.4000207@no_e-mail.com>,
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Harlan Grove
>Harlan Grove<hrl...@aol.com> wrote:
>>"round(x[, n])
>
>Yet there is Python's default algorithm used for FixedPoint:
>http://fixedpoint.sourceforge.net/html/lib/fixedpoint-rounding.html
>
>I don't know the language, though it looks *mighty* interesting.
We're talking about different things (no surprise). You're referring to the
FixedPoint class, which is implemented in an _optional_ module. I'm
referring to the round function built into the core language which works
with the basic floating point type.
Yes, Python is an interesting language. I beileve the gnumeric documentation
mentions how to write plug-ins in Python.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Harlan Grove
...
>Nor do I understand the concept of a 'tie.' At least to me, the rules
>for rounding are quite definitive.
They're conventions.
>Consider the range of numbers 1.5 <= x < 3.5. Measure numbers in this
>range at any desired degree of precision. I'll consider two examples.
>
>First, measure them at a single decimal value. We get 20 possible
>numbers 1.5, 1.6, ..., 2.0, ... 2.5, ... 3.0, ... 3.4
...
Your sample is skewed. Try 1.0 <= x <= 3.0 instead. On any interval that
includes both its endpoints, if it's divided into a even number of
subintervals, then there are an odd number of interval boundary points.
Using an interval that's neither open nor closed isn't representative of
most sampling spaces. Testing for bias on any interval that includes an odd
number of values with 5 as the digit after the least significant digit is
guaranteed to produced a biased average of rounded values. So . . .
1.0, 1.1, 1.2, 1.3, and 1.4 round to 1.0 in both types of rounding
1.6, 1.7, 1.8, 1.9, 2.0, 2.1, 2.2, 2.3, and 2.4 round to 2.0 in both types
of rounding
2.6, 2.7, 2.8, 2.9, and 3.0 round to 3.0 in both types of rounding
The average of the rounded subpopulation of just these points is 2.0. Now
how about the other points: 1.5 and 2.5? If they both round up, then 1.5
round to 2.0 and 2.5 to 3.0, and the average of this rounded subpopulation
is 2.5. Then the average of the rounded population as a whole is 2.0 *
(19/21) + 2.5 * (2/21) = 2.0476190... On the other hand, bankers' rounding
rounds 1.5 and 2.5 to 2.0, so the average of this rounded subpopulation is
the same as that of the other subpopulation, so the population as a hole has
average rounded value 2.0.
That said, if there's an equal frequency of positive and negative values
with identical distributions of absolute values, then both types of rounding
should be unbiased.
>Unless there is evidence to the contrary, I see no reason to disbelieve
>the possibility that some banker came up with this method to calculate
>interest that should be paid to customers.
Warning - sarcasm ahead!
So you know better than the folks who made bankers' rounding ASTM and IEEE
standards? If you really think so, PUBLISH! Show them up for the fools
and/or charlatans that you must believe they are.
Harlan Grove
>Both the rounding that you are familiar with (does anyone know of a
The Python link Dave provided uses the term 'add half and chop'. I've heard
it referred to as 'add and truncate' [with the 'half' being implicit]. The
main trick to it is specifying that 5 is rounded away from zero so
ABS(ROUND(x)) = ROUND(ABS(x)). So it should always look something like
SIGN(x)*INT(ABS(x)+0.5).
Tushar Mehta
says...
> Warning - sarcasm ahead!
>
> So you know better than the folks who made bankers' rounding ASTM and IEEE
> standards? If you really think so, PUBLISH! Show them up for the fools
> and/or charlatans that you must believe they are.
>
<shrug> If all of us worshipped at the Church of the Wisdom of the
Establishment, this discussion might not be about rounding but about
far we have to go before falling off the edge of the world.
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <OX75NwC4...@TK2MSFTNGP10.phx.gbl>, hrl...@aol.com
says...
Tushar Mehta
What you write makes sense -- in a manner of speaking.
[The terminology that follows might make a real
mathematician/statistician wince at its sloppiness, but I am not going
to revisit my textbooks from a decade ago -- that would make *my* head
hurt. <g>]
Use of the ROUND function aggregates the raw data. If the aggregated
data are to be used for further analysis and if the kind of analysis is
known, it makes sense to use the appropriate aggregation function. The
use of AVERAGE, for example, or the calculation of the delta, as in
your example, lend themselves to the use of banker's rounding.
However, if one wanted to use the FREQUENCY function on the aggregated
data, it might make more sense to use normal rounding.
[As an aside, this also reminds me of what one of my math/statistics
teachers told me. There is no guarantee that f(g(x)) = g(f(x)). So,
AVERAGE(ROUND(...)) is not necessarily = ROUND(AVERAGE(...)) or even
AVERAGE(...). It seems to me that the basic argument I'm reading is
that we know that g() introduces a bias into our f(), so we will use a
g1(), which is g() with this-one-twist.]
In my mind, it seems that we have something analoguous to the need for
multiple ways of calculating the average -- the MEAN (arithmetic and
geometric), the MEDIAN, and to a lesser extent, the MODE.
The (arithmetic) mean makes sense for some kinds of analyses, the
median for other. Given that we have multiple ways of calculating the
'average,' one can use the method better suited to one's goal to argue
that "the average tax cut will be over a thousand dollars."
So, I guess this discussion broadened my knowledge to the extent that
it seems that when it comes to rounding, one needs to consider both
(multiple?) types of rounding algorithms -- 'normal rounding' and
'bankers rounding'. Or, are the others in this discussion sold on the
unconditional superiority of bankers rounding to normal rounding?
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <010320031137052694%jemcg...@mvps.org>,
jemcg...@mvps.org says...
J.E. McGimpsey
nearly all purposes involving aggregation, BR is never inferior (for
sets of data with equal positive and negative values, there's no
advantage). XL's rounding has two advantages that I can see:
1) It's the method that I, and nearly anyone I've ever discussed this
with, learned in grade school, so it's familiar.
2) It's much easier to write algorithms that depend on a consistent
direction of rounding.
In article <MPG.18cb04d7b...@msnews.microsoft.com>, Tushar
Dana DeLouis
HOWTO: Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;196652
Bankers Rounding is mentioned a few times.
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.18cac0786...@msnews.microsoft.com...
Norman Harker
Thank you Dana! I've followed this discussion since Dave threw his size 16
spanner into the system. What I was interested in was finding solutions in
terms of how to get a Bankers Rounding return. I get general purpose UDF
using the same arguments as ROUND using:
Function BANKROUND(Number As Double, Digits As Integer) As Double
BANKROUND = Round(Number, Digits)
End Function
Nice and simple since VBA's Round function uses Bankers Rounding.
Microsoft's KB 196652 provides a more complex approach using a factor as the
second argument:
Function BRound(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
' For smaller numbers:
' BRound = CLng(X * Factor) / Factor
Dim Temp As Double, FixTemp As Double
Temp = X * Factor
FixTemp = Fix(Temp + 0.5 * Sgn(X))
' Handle rounding of .5 in a special manner
If Temp - Int(Temp) = 0.5 Then
If FixTemp / 2 <> Int(FixTemp / 2) Then ' Is Temp odd
' Reduce Magnitude by 1 to make even
FixTemp = FixTemp - Sgn(X)
End If
End If
BRound = FixTemp / Factor
End Function
On the question of bias it seems clear to me that there is a clear upwards
bias in ROUND's algorithm but less clear to me is the Microsoft comment in
KB 196652 that Bankers Rounding still has a bias. No doubt a statisticians
'thing' but for my general finance purposes I don't think it is going to be
very relevant. More important to me was the caution about using random
rounding methods to avoid that bias but which might produce two different
totals for the same data.
I've been doing some internet searching on the question and came up with the
following:
As official references we have:
ISO 31-0 :1992, Quantities and units - Part 0 : General principles, Annex B
(Informative) (Guide to the rounding of numbers) Priced CHF 95 (I’m
surprised it hasn’t been rounded to CHF 100 <vbg>)
http://www.iso.ch/iso/en/ISOOnline.frontpage
SI10-2002 IEEE/ASTM Standard for Use of the International System of Units
(SI): The Modern Metric System 2002 SI10-2002 IEEE/ASTM Standard for Use of
the International System of Units (SI): The Modern Metric System 2002. (I
think all we need is Annex B Rules for Conversion and Rounding). Priced
US$50
http://shop.ieee.org/store/default.asp?tabtype=stand
I haven't been able to track down a free copy of just the Annex B although I
did find a Cantonese site that I think was a translation and the examples
squared with the interpretation of the method that everyone has been
referring to under the name "Bankers Rounding". The text was all Chinese to
me!
Now to look at a formula approach to get a Bankers Rounding with number in
A1 and Digit for rounding in B1?
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Dana DeLouis" <ng_...@hotmail.com> wrote in message
news:esNgrpG4...@TK2MSFTNGP09.phx.gbl...
Tushar Mehta
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <esNgrpG4...@TK2MSFTNGP09.phx.gbl>, ng_...@hotmail.com
says...
Tushar Mehta
njha...@optusnet.com.au says...
>
> On the question of bias it seems clear to me that there is a clear upwards
> bias in ROUND's algorithm but less clear to me is the Microsoft comment in
> KB 196652 that Bankers Rounding still has a bias. No doubt a statisticians
No, it is not a statistician's 'thing.' If you have numbers uniformly
distributed, then after banker's rounding they will not be so.
Consider 20 numbers: 1.5, 1.6, 1.7, ... 3.4. After normal rounding the
distribution will be 10 elements of value 2, 10 of value 3. After
bankers rounding, the distribution will be 11 of value 2, 9 of value 3.
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <ujkXOoH4...@TK2MSFTNGP12.phx.gbl>,
njha...@optusnet.com.au says...
Jerry W. Lewis
David,
I received 3 private e-mails from you at my attbi address. I replied
Friday night and Saturday morning. Did you receive any of my replies?
Jerry
Jerry W. Lewis
Function BANKROUND(Number As Double, Digits As Integer) As Double
BANKROUND = Round(CDbl(CStr(Number)), Digits)
End Function
The VBA Round() function is not as well buffered against the vaguaries
of binary approximations as the worksheet ROUND() function, but
CDBL(CSTR()) ensures that rounding is consistent with the 15 decimal
digit representation.
Jerry
Norman Harker
Thanks for that Tushar! I was misinterpreting the meaning of 'bias' It's not
biased either upwards or downwards but is biased in the way you explained.
From a straight finance 'equity' viewpoint it seems that Banker's Rounding
would be fairer in terms of winners and losers of rounding. There are still
some areas of potential problems if (for example) prices are commonly set at
19.95 and we use Bankers Rounding but that's due to distortion of our data
rather than our rounding method.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.18cbc2a39...@msnews.microsoft.com...
Norman Harker
Thanks Jerry!
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3E6200C3.8020802@no_e-mail.com...
Sandy Mann
> Function BANKROUND(Number As Double, Digits As Integer) As Double
> BANKROUND = Round(Number, Digits)
> End Function
>
> Nice and simple since VBA's Round function uses Bankers Rounding.
I get a "Compile Error Sub or Function not defined" on the above with
*Round* highlighted. Was a VBA Round() added after XL97 SR1?
Regards,
Sandy
<snip>
Dave Peterson
Sandy Mann
That's the story of my life - always the poor relation. <g>
Regards
Sandy
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3E623255...@msn.com...
David J. Braden
What in the world does *that* establish?!? You're stating that binning
data rounded with one convention doesn't end up where you would like.
Fine. Your claim that what you term "normal" rounding bins correctly
strikes me as fabulous.
Start your sequence at 1.4 instead. Then after either of the rounding
algorithms being discussed, the data will not be uniformaly distributed.
So what? I think you are on a fruitless path with this line of reasoning.
Dave Braden
In article <MPG.18cbc2a39...@msnews.microsoft.com>,
Tushar Mehta <ng_p...@bigfoot.com> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
David J. Braden
Thanks for the countervailing suggestions.
more...
In article <010320031645244349%jemcg...@mvps.org>,
"J.E. McGimpsey" <jemcg...@mvps.org> wrote:
> Don't know about the others, but my own understanding is that for
> nearly all purposes involving aggregation, BR is never inferior (for
> sets of data with equal positive and negative values, there's no
> advantage).
Nicely put. I too don't know of a case where it isn't at least as good
as the current ROUND algorithm.
> XL's rounding has two advantages that I can see:
>
> 1) It's the method that I, and nearly anyone I've ever discussed this
> with, learned in grade school, so it's familiar.
Yep. And that familiar formula we learned for calculating
Variance(data), which is mathematically exact, doesn't really cut it
either for actual computations, but we can now use computers to do a
better job of the thing (ok, not Excel's VAR-related functions, but you
get the idea).
Looks like so-called "banker's rounding" is getting more familiar. It
would be a pain to apply by hand in ledgers of data, but today? Easy.
>
> 2) It's much easier to write algorithms that depend on a consistent
> direction of rounding.
Huh? "Consistent direction"? BR certainly provides that. It is a
deterministic convention. Could you provide an example here of where it
gets in the way of writing algorithms?
TIA
Dave Braden
>
>
> In article <MPG.18cb04d7b...@msnews.microsoft.com>, Tushar
> Mehta <ng_p...@bigfoot.com> wrote:
>
> > So, I guess this discussion broadened my knowledge to the extent that
> > it seems that when it comes to rounding, one needs to consider both
> > (multiple?) types of rounding algorithms -- 'normal rounding' and
> > 'bankers rounding'. Or, are the others in this discussion sold on the
> > unconditional superiority of bankers rounding to normal rounding?
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Norman Harker
Thanks for that. Poor relations need feeding as well and I can't have a
function that doesn't work on Excel 97 so try an adapted version of the
Microsoft's KB 196652 BRound.
Function BANKROUND(Number As Double, Digits As Integer) As Double
Dim Temp As Double, FixTemp As Double
Temp = Number * 10 ^ (Digits)
FixTemp = Fix(Temp + 0.5 * Sgn(Number))
If Temp - Int(Temp) = 0.5 Then
If FixTemp / 2 <> Int(FixTemp / 2) Then
FixTemp = FixTemp - Sgn(Number)
End If
End If
BANKROUND = FixTemp / 10 ^ (Digits)
End Function
Seems to work OK on Excel 97 and still satisfy the richer relations. I'll
leave Jerry to decide if the code needs amending for the "vaguaries of
binary approximations" plus other areas where I commit various other heinous
crimes :(
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Sandy Mann" <sand...@tiscali.co.uk> wrote in message
news:#9NbkRO4...@TK2MSFTNGP11.phx.gbl...
Norman Harker
I won't presume upon a maths / stats discussion but I wonder what Microsoft
were referring to when in KB 196652 they say "Even banker's rounding can
bias totals."
My concern is one of producing more equitable rounding than ROUND in terms
of just as many prices being rounded up as rounded down. I think I'm OK on
that concern but that's what we pay you guys for <vbg>
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"David J. Braden" <see...@real.address.below> wrote in message
news:see_sig-8EAD2D...@msnews.microsoft.com...
J.E. McGimpsey
J. Braden <see...@real.address.below> wrote:
> Huh? "Consistent direction"? BR certainly provides that. It is a
> deterministic convention. Could you provide an example here of where it
> gets in the way of writing algorithms?
Perhaps instead of "consistent" I should have said "uniform". I don't
have any examples to hand, but instead of aggregation I was thinking of
pricing models that clients have used which treated prices in bands,
say: $X.00-$X.45, $X.45-$X.49, $X.50-$X.95 and $X.95-$X.99
Being able to use ROUND and get the same behavior for $0.45 and $0.95
might be useful.
Of course, that could be accomplished in a variety of other ways...
David J. Braden
"J.E. McGimpsey" <jemcg...@mvps.org> wrote:
Ahhh, that sure makes sense. Sheesh, I should have thought of that
possibility, though when it comes to nonlinear pricing I don't use ROUND.
In any case, seems like I've seen enough call for it to be a nice
feature request for Excel.
Once Harlan metioned Delphi uses such rounding, it occured to me that it
had found its way into C# (since its chief designer was also responsible
for Delphi). . Looks like it pervades .NET.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/ht
ml/frlrfSystemDecimalClassRoundTopic.asp
Interesting, eh?
Dave B
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Sandy Mann
Sandy
"Norman Harker" <njha...@optusnet.com.au> wrote in message
news:urUUJqP4...@TK2MSFTNGP11.phx.gbl...
Jerry W. Lewis
>
> I won't presume upon a maths / stats discussion but I wonder what Microsoft
> were referring to when in KB 196652 they say "Even banker's rounding can
> bias totals."
If all fractions are equally likely, then banker's rounding is unbiased.
If some fractions are more likely than others (such as .95 for prices)
then you have to ask whether it might be biased in the context of that
particular kind of data.
Jerry
Norman Harker
I'd thought that was the case. That pricing bias certainly exists as we can
all see in the supermarkets. But Aussie rounding regulations require that it
is done at bill rather than item level. The regulation came in when we
dropped 1c and 2c coins and rounding is to the nearest 5c. The individual
pricing of items at 99c is very common. To a retail chain a 1c extra on the
margin represents quite a profit chunk.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3E629486.1030202@no_e-mail.com...
Norman Harker
No problems!
It's important that functions like this don't disappear in error messages
when run on a computer with other versions of Excel and I hadn't tested it
on my (t)rusty copy of Excel 97.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Sandy Mann" <sand...@tiscali.co.uk> wrote in message
news:eaonuIR4...@TK2MSFTNGP11.phx.gbl...
Tushar Mehta
I agree that in a limited field of operation (where one wants to
aggregate the aggregated data using the arithmetic mean function),
bankers rounding is indeed better than normal rounding.
However, that one example doesn't establish the universal superiority
of the function.
If the underlying data come from an uniform distribution, the result of
normal rounding is superior to that of bankers rounding in preserving
*that* characteristic of the data.
If the desired 2nd aggregation function is variance (rather than
arithmetic mean), there seems no clear cut relationship i.e.,
VAR(Xi) > Var(Round(Xi)) > Var (BR(Xi)) or
VAR(Xi) > Var(BR(Xi)) > Var (Round(Xi))
Nor is there one in calculating a weighted average where the original
data (and the corr. rounded numbers) constitute the weights.
If it were true that bankers rounding was universally superior to
normal ronnding, then we would have a function that satisfied, for
every aggregation function, f, the relationship:
|f(Xi) - f(BR(Xi))| <= |f(Xi) - f(Round(Xi))|
(I hope I used the absolute functions correctly <g>).
Care to prove that?
--
Regards,
Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel
In article <see_sig-8EAD2D...@msnews.microsoft.com>,
see...@real.address.below says...
Jerry W. Lewis
Number = CDec(CStr(Number))
Right after the Dim statement. As an example of why, consider the
problem that prompted the big "Excel can't do math (substract)" thread
in microsoft.public.excel.misc starting last Thursday: Put 0.12 in A1,
=A1-0.005 in A2 and copy A2 down the column starting. New twist: now
try to copy BANKROUND(A1,2) down the column. Without the above line,
BANKROUND will not recognize that any of 0.095, 0.075, 0.055, 0.035 or
.015 have exactly 5 in the 3rd figure, even though the worksheet ROUND()
does, and Edit|Paste Special shows no discrepancies to 15 figures.
Jerry
Norman Harker
Thank you! I thought you would want that but didn't want to usurp your
original suggestion. But apart from that I still need a bit of help below on
a formula solution and would appreciate your suggestions on improving that
one too.
The much more robust approach as a function result is the following:
Function BANKROUND(Number As Double, Digits As Integer) As Double
Dim Temp As Double, FixTemp As Double
Number = CDec(CStr(Number))
Temp = Number * 10 ^ (Digits)
FixTemp = Fix(Temp + 0.5 * Sgn(Number))
If Temp - Int(Temp) = 0.5 Then
If FixTemp / 2 <> Int(FixTemp / 2) Then
FixTemp = FixTemp - Sgn(Number)
End If
End If
BANKROUND = FixTemp / 10 ^ (Digits)
End Function
Now! Since I like on-sheet formula solutions, I get the following bankers
rounding formula from the above excluding your amendment:
=IF(AND(A1*10^B1-INT(A1*10^B1)=0.5,(C1+(0.5*SIGN(A1)))/2<>INT((C1+(0.5*SIGN(
A1)))/2)),((C1+(0.5*SIGN(A1)))-SIGN(A1))/10^B1,(C1+(0.5*SIGN(A1)))/10^B1)
This doesn't incorporate your suggestion and I'm pretty certain that is why
it produces quite a few errors in comparison with the use of the BANKROUND
function. Suggestions?
I'm sure that I could use a monkey wrench to cover the issue but your micro
surgery might be a better approach.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3E62A253.40700@no_e-mail.com...
r l n g r v @aol.com H.G.
..
>Now! Since I like on-sheet formula solutions, I get the following bankers
>rounding formula from the above excluding your amendment:
>
>=IF(AND(A1*10^B1-INT(A1*10^B1)=0.5,(C1+(0.5*SIGN(A1)))/2<>INT((C1+(0.5*SIGN(
>A1)))/2)),((C1+(0.5*SIGN(A1)))-SIGN(A1))/10^B1,(C1+(0.5*SIGN(A1)))/10^B1)
Ugh! You need to learn the idiom
=SIGN(x)*SomeOtherOperation(ABS(x))
You're guaranteed biased results if bround(abs(x)) <> abs(bround(x)).
>This doesn't incorporate your suggestion and I'm pretty certain that is why
>it produces quite a few errors in comparison with the use of the BANKROUND
>function. Suggestions?
..
So what are these errors/discrepancies?
The formula approach is simple for 'strict' bankers rounding. The analog for
ROUND(x,n) is
=SIGN(x)*ROUND(ABS(x)-(MOD(ABS(x)*10^n,2)=0.5)*0.5/10^n,n)
However, consider this: if rounding to 1 place to the right of the decimal
point, should 1.45000000000001 round up to 1.5 or down to 1.4? Strict bankers
rounding would imply it should round up to 1.5. But the calculation used to
determine that is subject to floating point rounding error. This complication
can be dealt with only by specifying what to do if all bits below the largest
bit corresponding to the largest negative power of 2 less than the least
significant bit [so for rounding to 1/10, that would be bit 4 (1/16) of the
fractional part] were all 1 (in which case binary round up by adding 1/16) or
all but the least significant bit were zero (in which case chop off that least
significant bit).
Specifying bankers rounding for decimal fixed point is hugely simpler than
specifying it for binary floating point.
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
Harlan Grove
><shrug> If all of us worshipped at the Church of the Wisdom of the
>Establishment, this discussion might not be about rounding but about
>far we have to go before falling off the edge of the world.
..
Granted. So build up your case against these 'standards', and publish it. Or
perhaps a better approach would be to read the respective standards since they
should contain the mathematical rationale behind bankers rounding.
Oh, and just to emphasize this, the case for bankers rounding would be purely
mathematical, so you'd need to find invalid steps in the proofs or attach the
axioms. Good luck. With regard to received wisdom, ancient 'knowledge' of the
world and universe were based on flawed methods of observation, and much was
overturned when better methods were found. How many mathematical theorems proven
by the ancients have been refuted by modern mathematicians?
David J. Braden
Huh? Mine seems to be in serious technicolor compared to your view <g>.
I'm burning out on this topic. You seem intransigent in your outlook. So
be it. Perhaps as you come to use it more you will be more comfortable
with it.
As Dave Patterson noted, the VBA Round in recent versions of Excel uses
BR; Harlan verified that Delphi does too. And from what I can tell, it
is the default for anything working within .NET (C#, Java, ...). It
strikes me as very sensible.
As stated, no rounding algorithm will maintain a uniform aggregation
over an arbitrary uniformly spaced set of data for a given nontrivial
bin-set. How you can claim that "normal" rounding is superior in this
regard is beyond me. I posted one simple counter-example to show that
the metric itself is useless.
Re your penultimate paragragh: Who is looking for univeral superiority?
We've already established that what you term "normal" rounding isn't.
And you have yet to provide a robust, realistic example of where it *is*
superior. Good luck on that one.
Later,
Dave B
In article <MPG.18cc69cd6...@msnews.microsoft.com>,
Tushar Mehta <ng_p...@bigfoot.com> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
David J. Braden
> world and universe were based on flawed methods of observation, and much was
> overturned when better methods were found. How many mathematical theorems
> proven
> by the ancients have been refuted by modern mathematicians?
>
Worse yet is when a colleague (who was likely on Tushar's thesis
commitee) discovered a mistake in the appendix for my thesis after it
garnered Stanford's good-housekeeping seal. At least "wisdom of the
ancients" lasted for more than a few years. Groan.
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Harlan Grove
..
>As Dave Patterson noted, the VBA Round in recent versions of Excel uses
>BR; Harlan verified that Delphi does too. And from what I can tell, it
>is the default for anything working within .NET (C#, Java, ...). It
>strikes me as very sensible.
Java? As part of the BigDecimal class? Specific to Microsoft implementations of
Java? When it comes to Java, an argument could be made to pay more attention to
Sun than to Microsoft for specs.
It may be that languages other than C# that work in the .Net framework do so
using classes that encapsulate rounding methods that implement bankers' rounding
rather than incorporating bankers' rounding in the core language for use with
basic floating point types.
Stephen Dunn
I've been following this thread in awe of all the big names in it, so it's
with some trepidation that I stick my nose in.
I thought that I'd been following it fairly well, and your last formula
agreed with the way I'd been simplifying it to myself: If nth place digit
is even and the next is 5, remove the 5, then round as normal. ABS allows
the 5 to be removed by subtraction, and SIGN puts it right again. Right?
However, your comments about 1.45000000000001 stuck a spanner in my train of
thought. Why would 'strict' bankers rounding imply that this became 1.5?
Note that I have no previous experience of BR, so this is a genuine query,
not a critique.
Regards,
Steve D.
"H.G." < h r l n g r v @ a o l . c o m > wrote in message
news:l6P8a.801$4F4...@www.newsranger.com...
David J. Braden
Harlan Grove<hrl...@aol.com> wrote:
> "David J. Braden" wrote...
> ..
> >As Dave Patterson noted, the VBA Round in recent versions of Excel uses
> >BR; Harlan verified that Delphi does too. And from what I can tell, it
> >is the default for anything working within .NET (C#, Java, ...). It
> >strikes me as very sensible.
> ..
>
> Java? As part of the BigDecimal class? Specific to Microsoft implementations
> of
> Java? When it comes to Java, an argument could be made to pay more attention
> to
> Sun than to Microsoft for specs.
>
> It may be that languages other than C# that work in the .Net framework do so
> using classes that encapsulate rounding methods that implement bankers'
> rounding
> rather than incorporating bankers' rounding in the core language for use with
> basic floating point types.
Harlan,
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/ht
ml/frlrfSystemDecimalClassRoundTopic.asp
JScript, not Java. Sorry. A point is that it seems endimic to .NET
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
David J. Braden
It's because the portion of the number to the right of the digit we are
rounding to isn't exactly 0.5.
FWIW, I found a more appropriate term for this in online documentation
for Microsoft's and Sourceforge's documentation: "rounding to even";
seems more descriptive, and risks no chance of hallowing bankers. <g>
HTH
Dave Braden
"Stephen Dunn" <st...@graydon-dawson.freeserve.co.uk> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Stephen Dunn
I think that counts as a Doh! from me. I should have noticed that it was
=.5 rather than >=.5 in Harlan's formula, (and that clears up my final
doubts about this system. I must admit that I was tending to agree with
Tushar).
As for hallowing bankers: ROFLMAO!
Steve D.
"David J. Braden" <dbr...@real.address.below> wrote in message
news:dbraden-204EA1...@msnews.microsoft.com...
<snip>
David J. Braden
Please, please don't succumb to his uncharacteristically hide-bound
handwaving. To paraphrase what Harlan posted, "things improve".
I initiated this whole "BR" hoopla, in response to John McGimpsey's
post, primarily because I believe BR to be the most appropriate default
for a package that, when not used simply for holding lists, is likely
most often used in financial-services-related applications, where BR
reigns (or should?). That so-called "BR" seems to have great potential
in other apps as well intrigues me. John McGimpsey alluded to its
usefulness in apps he has developed and used in Natural Sciences.
Big lesson here is that what we learn in gradeschool may not be so
robust. In statistics, we have seen *huge* changes in the last 20
years, thanks to improvements in computational power, and a lot of
serious insight. E.g., Bradly Effron and others developed
"bootstrapping", freeing us from goofy assumptions we made earlier for
mathematical tractability. The result? Much more robust results that
are a lot easier to interpret. Yet has this made it into the typical
undergrad stats course? Nope. It renders much of the elegant
mathematics, and investment the profs made in their education, pointless.
I keep my eyes wide open for such developments. That they haven't
filtered down to 1st-grade level doesn't concern me when it comes to
doing the best I can by my clients.
Keep posting!!
Dave Braden
Microsoft MVP
In article <ObwUBbk4...@TK2MSFTNGP12.phx.gbl>,
"Stephen Dunn" <st...@graydon-dawson.freeserve.co.uk> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Harlan Grove
..
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
>frlrfSystemDecimalClassRoundTopic.asp
>
>JScript, not Java. Sorry. A point is that it seems endimic to .NET
Note that it's a method of the Decimal class rather than a function per se, and
it doesn't work _directly_ on binary floating point values.
Per my comments elsewhere in this thread, rounding to even is far easier to
specify for [fixed point] decimal values than binary floating point values. The
real work done in .Net would be converting values back & forth between decimal
and binary floating point. Once a value has been converted to decimal, it's
almost trivial to round to even.
Binary floating point, however, requires some consideration of whether all low
order bits are 1 or all but the last is 0. The problem is that figuring whether
the decimal digit below the intended least significant decimal digit is 5
requires a floating point calculation, in which case does it really make sense
to consider ((1.45000000000001 * 100) Mod 2) > 0.5 ?
It's a good thing that .Net seems to mandate a decimal class. It's not a bad
thing that .Net seems to mandate that the decimal class contain a rounding
method that performs round to even. It's unclear this implies anything at all
about how any .Net language rounds binary floating point values.
I know I'm being a PITA about which type(s) these round to even procedures
apply, but I consider it important to point out (repeatedly if necessary) that
giving [likely fixed point] decimal value classes round to even methods doesn't
mean it would be easy to provide round to even *decimal* least significant digit
procedures for binary floating point values. It still appears to me that only
Delphi provides this for its basic (non-object) binary floating point type.