Bankers Rounding
-
Thanks Jeff, it does. And credit to Sergiy and Lynn for spotting it. For the record I did not want to start a "Holy War", we've got enough of those as it is.
I just wanted clarification as to why, given a certain test cases, it failed to give the expected answer. Now I know. And as others have pointed out, using the correctly sized decimal data type fixes it. So many thanks to all, now we can get back to work
Cheers
Dave J
-
P.S That's two pints I owe you. As well as Lynn, Antares686, Sergiy and the other who have contributed.
Dave J
-
The corrected Function
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM SYSOBJECTS
WHERE NAME = N'fn_BRound')
DROP FUNCTION fn_BRound
GO
CREATE FUNCTION fn_BRound
(@p1 DECIMAL(30,20),
@p2 INT)
RETURNS MONEY
/*
Round-to-even method (from http://en.wikipedia.org/wiki/Rounding)
This method is also known as unbiased rounding or as statistician's rounding or as bankers' rounding.
It is identical to the common method of rounding except when the digit(s) following the rounding digit
start with a five and have no non-zero digits after it. The new algorithm is:
* Decide which is the last digit to keep.
* Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits.
* Leave it the same if the next digit is 4 or less
* Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes;
then change the last digit to the nearest even digit. That is, increase the rounded
digit if it is currently odd; leave it if it is already even.
With all rounding schemes there are two possible outcomes: increasing the rounding digit by one or
leaving it alone. With traditional rounding, if the number has a value less than the half-way mark
between the possible outcomes, it is rounded down; if the number has a value exactly half-way or
greater than half-way between the possible outcomes, it is rounded up. The round-to-even method is the
same except that numbers exactly half-way between the possible outcomes are sometimes rounded up—sometimes down.
Although it is customary to round the number 4.5 up to 5, in fact 4.5 is no nearer to 5 than it is to 4
(it is 0.5 away from either). When dealing with large sets of scientific or statistical data, where trends
are important, traditional rounding on average biases the data upwards slightly. Over a large set of
data, or when many subsequent rounding operations are performed as in digital signal processing, the
round-to-even rule tends to reduce the total rounding error, with (on average) an equal portion of numbers
rounding up as rounding down. This generally reduces the upwards skewing of the result.
Round-to-even is used rather than round-to-odd as the latter rule would prevent rounding to a result of zero.
Examples:
* 3.016 rounded to hundredths is 3.02 (because the next digit (6) is 6 or more)
* 3.013 rounded to hundredths is 3.01 (because the next digit (3) is 4 or less)
* 3.015 rounded to hundredths is 3.02 (because the next digit is 5, and the hundredths digit (1) is odd)
* 3.045 rounded to hundredths is 3.04 (because the next digit is 5, and the hundredths digit (4) is even)
* 3.04501 rounded to hundredths is 3.05 (because the next digit is 5, but it is followed by non-zero digits)
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.fn_BRound (282.26545,
100) -- 100 to nearest penny, 20 to nearest 5 pence, 10 to nearest 10 pence,
-- 5 to nearest 20 pence, 2 to nearest 50 pence, 1 to nearest pound
Code (poorly) converted from VB example @ http://support.microsoft.com/kb/196652
*/
AS
BEGIN
DECLARE @Temp DECIMAL(30,20),
@FixTemp DECIMAL(30,20)
SELECT @Temp = @p1 * @p2
SELECT @FixTemp = SIGN(@Temp + 0.5 * SIGN(@p1)) * FLOOR(ABS(@Temp + 0.5 * SIGN(@p1)))
-- Handle rounding of .5 in a special manner
IF @Temp - FLOOR(@Temp) = 0.5
BEGIN
IF @FixTemp / 2 <> FLOOR(@FixTemp / 2) -- Is Temp odd
-- Reduce Magnitude by 1 to make even
SELECT @FixTemp = @FixTemp - SIGN(@p1)
END
RETURN @FixTemp / @p2
END
GO
SET NOCOUNT ON
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.fn_BRound(511.945,100) -- Right
SELECT dbo.fn_BRound(512.945,100) –- Now Right
SELECT dbo.fn_BRound(578.945,100) --any number between the one above and below, ending .945
SELECT dbo.fn_BRound(653.945,100) -- Now Right
SELECT dbo.fn_BRound(655.945,100) -- Right
--examples as mentioned in comment above
SELECT dbo.fn_BRound(3.016,100)
SELECT dbo.fn_BRound(3.013,100)
SELECT DBO.FN_BROUND(3.015,100)
SELECT dbo.fn_BRound(3.045,100)
SELECT dbo.fn_BRound(3.04501,100)
-
Here's a similar solution to Lynn's:
CREATE FUNCTION dbo.fn_BRound (
@TestValue decimal(38,12),
@pos int
)
RETURNS money
as begin
return
round(@TestValue,@pos,
case when floor(@TestValue*power(10,@pos))=floor(floor(@TestValue*power(10,@pos))/2)*2
and (@TestValue*power(10,@pos) - floor(@TestValue*power(10,@pos)))=.5
then
1
else
0
end)
-- Explanation
floor(@TestValue*power(10,@pos))=floor(floor(@TestValue*power(10,@pos))/2)*2
-- This expression checks to see if the number to round is even by using floor
-- i.e. use @TestValue=1.255 and @pos=2
-- floor(1.255*100) = 125 does this equal floor(floor(125.5)/2)*2,
-- floor(floor(125.5)/2)*2 = floor(125/2)*2 = floor(62.5)*2 = 62*2 = 124
-- this expression in this case will be false
-- This expression checks to see if the remaining digits = .5
(@TestValue*power(10,@pos) - floor(@TestValue*power(10,@pos)))=.5
-- i.e (1.255*100) - floor(1.255*100) = 125.5 - floor(125.5) = .5 = .5
-- This expression is true
-- Because 5 is odd, we will not truncate the rounded results, thus for the case statement, these values will use the value in the else clause.
-- MSDN Documentation on round function.
C. Use ROUND to truncate
This example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.
Statement Result SELECT ROUND(150.75, 0)151.00SELECT ROUND(150.75, 0, 1)150.00Regards,
MCTS
-
mengus,
Took a bit, but I see where you are coming from. It looks like a case of semantics between your modification to my solution. In mine, I choose to round if the difference is > 0.5 and your change says to truncate if the difference = 0.5.
Both work.
-
Yeap...
Almost the same logic, I chose my way simply so that I can explain, to the original post, the steps, mathematics and logic.
Regards,
MCTS
-
Sergiy, I did that, and it looks fair to me. 9900 values round up, while 9900 values round down. It's quite true that with banker's rounding an individual transaction with only a few line items can skew one way or the other. The idea is that over a large number of line items, each side will get an equivalent number of breaks in their favor.
DECLARE
@Money money
,@Step money
SELECT
@Money = 0.0000
,@Step = .0001
DECLARE @BRounding TABLE
(
Col1 money
,Col2 money
)
WHILE @Money < 2.000
BEGIN
INSERT INTO @BRounding
(
Col1
,Col2
)
VALUES
(
@Money
,dbo.fn_BRound(@Money, 100)
)
SET @Money = @Money + @Step
END
SELECT
RoundedDown = Count(*)
FROM
@BRounding
WHERE
Col1 > Col2
SELECT
RoundedUp = Count(*)
FROM
@BRounding
WHERE
Col1 < Col2
-
And missing 200 go in favor of ...?
_____________
Code for TallyGenerator -
No one, as those 200 don't require rounding. The 200 is comprised of numbers such as 0.0100, 0.0200, 0.300, etc.
So, we have 200 numbers where no rounding occurs, 9900 where they round up, 9900 where they round down. Sounds perfectly fair to me.
-
Heh.... I'm in the telephone business... case you haven't noticed, we round everything UP
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
So, for 2000 numbers between 0(including) and 0.2(excluding) after rounding you've got 1100 numbers equal to FLOOR and 900 equal to CEILING.
Fair?
_____________
Code for TallyGenerator -
That's incorrect, as both floor and ceiling return the same value for non-rounded numbers. Once again, equal numbers are rounded up and down, while another set of numbers aren't rounded at all. In the earlier example you had me code, there are 20,000 numbers. 9,900 rounded up, 9,900 rounded down, and 200 didn't round at all, therefore 10,100 are equal to floor (9,900 which rounded down + all 200 of the non-rounded numbers) and 10,100 (9,900 which rounded up + all 200 of the non-rounded numbers) are equal to ceiling, as non-rounded numbers return identical results whether you use floor or ceiling.
So yes, fair.
You asked for me to perform an experiment for which you gave the parameters, which I did. It demonstrated that my original statement was accurate. Are you still claiming otherwise?
-
Thanks everybody for an educational and fun to watch discussion. I am not in the banking business but I will 'store' the functions provided in my 'treasure chest' because one never knows what the future might bring.
I will try to explain why the bankers rounding is perfectly correct from the mathematical point of view - at least the way I understand this so if I am wrong please let me know.
The bankers rounding is not a calculus (or whatever part of math this is) function, it is statistical function. Banks can not make or lose money when performing fractional transactions (taxes, etc). So bankers rounding was created so when you take random, large number of transaction then from the statisticsl point of view you will end up with the same number of transctions rounded up and transactions rounded down. It is not only fair but I think it is also legal.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Here comes the diffeence between banking and real world.
There is no such thing as precise numbers in real world.
Length of precise number interval on any axis = dx -> 0.
So, probability of anything having exactly defined measurement -> 0, that means impossible.
Bankers rounding uses assumption of precise numbers. What brings you some funny results.
You cannot even count your cases correctly. You come up with either 19800 or 20200 numbers, but not with 20000.
It's very interesting - where bankers put an event happened on midnight: yesterday or tomorrow?
Did it happen twice (both FLOOR and CEILING) or never (not rounded) ?
Does not make any sence when it comes to real world, right?
That's why I strongly opposed the statement in initial post mentioned "large sets of scientific or statistical data". Using Bankers Rounding for these purposes is absolutely inappropriate.
Yes, I admit, Bankers Rounding can help to solve some problems with fractional distributions. But is it universal method?
I posted 2 examples when this method does not work: $1 distributed over 3 items and NZ invoice. Noone can show me how BR could work it out.
So, it cannot be considedred as a standard.
From another side, problems resolved by BR could be easily resolve by using 4-digits money type for storing and calculating.
It does not help you with corect presentation of 1/3 interim result but it always brings you right totals.
And it does not let you fail in "NZ invoice" case.
_____________
Code for TallyGenerator -
"Here comes the diffeence between banking and real world."
Banker's rounding is used in the "real world", whatever that means.
"There is no such thing as precise numbers in real world."
Of course there are. Even in the test you set forth (which once again, proved that you were incorrect, although you haven't admitted it yet), there were 200 precise numbers when our goal was to bring the numbers to hundredths.
"Bankers rounding uses assumption of precise numbers. What brings you some funny results."
It makes no such assumption. It simply fixes a flaw with other rounding types, where a number exactly halfway between two acceptable points goes in a single direction. By splitting the numbers in half, sending each group in a different direction, banker's rounding compensates for that flaw.
"You cannot even count your cases correctly. You come up with either 19800 or 20200 numbers, but not with 20000.
"I have no problem whatsoever coming up with 20,000 numbers, and did so earlier. 9900 rounded down, 9900 rounded up, and 200 not rounded at all. Looks like 20,000 to me.
"It's very interesting - where bankers put an event happened on midnight: yesterday or tomorrow? Did it happen twice (both FLOOR and CEILING) or never (not rounded) ? Does not make any sence when it comes to real world, right?"
I have no idea what you think that has to do with this rounding method.
"That's why I strongly opposed the statement in initial post mentioned "large sets of scientific or statistical data". Using Bankers Rounding for these purposes is absolutely inappropriate."
No, it's not inappropriate. If you must round your numbers for any reason, and if you want that rounding to not slightly skew your results in one direction over a large distribution of numbers, it's a wonderful solution. If you don't need to round your numbers, aren't allowed to round your numbers, or if you don't care about that minor skewing, then certainly, choose an easier to implement method.
"Yes, I admit, Bankers Rounding can help to solve some problems with fractional distributions. But is it universal method?
I posted 2 examples when this method does not work: $1 distributed over 3 items and NZ invoice. Noone can show me how BR could work it out.
So, it cannot be considedred as a standard."
$1 distributed over 3 items has nothing to do with banker's rounding, as .333... which would always round down just fine with the round function. If you happen to work in a "Everything in the Store is 3 Items for $1" shop, this is a problem, but still has nothing to do with banker's rounding. On the other hand, in the "real world" which you so like to speak of, the next invoice could easily be 3 items for $2, in which case, all items would have rounded up, thus evening out the distribution. Again, this has zip to do with banker's rounding, which is what we are discussing here.
"From another side, problems resolved by BR could be easily resolve by using 4-digits money type for storing and calculating.
It does not help you with corect presentation of 1/3 interim result but it always brings you right totals.
And it does not let you fail in "NZ invoice" case."
Actually, using 4 digits money type still would require banker's rounding, if you cared about those 4 digits, and wanted to remain fair. In order to keep the number of digits to 4, rounding of some sort would often have to occur, and that fourth digit would be ever so slightly skewed in one direction, unless one used banker's rounding or a similar algorithm.
Viewing 15 posts - 46 through 60 (of 373 total)
You must be logged in to reply to this topic. Login to reply