Round to Even (aka Banker''s Rounding) - The final function
-
Cheers Chris, you're right of course. David, David, Noel, Lynn et al are doing a sterling job in the face of adversity. Their patience and perseverence are a shining example of some of the (non-mathematical) skills IT professionals need. Hats off to you guys!
-
erm! did you not state that
1/3 is
(int) 1 divided by (int) 3
however:-
1.0 divided by 3.0 as (money + implied zeros) = 0.3333(0)
select cast(1.0 / 3.0 as money)
1.0 divided by 3.0 as (decimal(12,6) + implied zeros) = 0.333333(0)
select cast(1.0 / 3.0 as decimal(12,6))
0.3333(0) - 0.333333(0) = -.000033(0)
select cast(1.0 / 3.0 as money) - cast(1.0 / 3.0 as decimal(12,6))
if 0.3333 = 0.3333 print 'yes' else print 'no'
if 0.3333 = 0.33330 print 'yes' else print 'no'
if 0.3333 = 0.333300 print 'yes' else print 'no'
if 0.3333 = 0.3333000 print 'yes' else print 'no'
if 0.3333 = 0.33330000 print 'yes' else print 'no'
Far away is close at hand in the images of elsewhere.
Anon. -
Cath,
as soon as you started off-topic, out of curiosity - what are those interesting things you have learnt about mathematics from this thread?
_____________
Code for TallyGenerator -
Jeff,
"Futility. That there's a time when you should just give up"
There's a lot of truth in this one. To be honest, the only reason I've stuck with this so long is that Sergiy actually has a lot of what I would consider useful knowledge in the SQL world, and his advice should often be taken into consideration, no matter how he delivers it (he's also wrong at times, as are all of us). Since his SQL skills are obvious to anyone with even a passing knowledge of SQL, my concern is that if left unchallenged, his word here will be taken as gospel by people who are still learning, and struggle with some of the concepts presented. His magical Round() function, for instance, sounds really cool, but unfortunately, that's not how it works. As you and I both know, it simply takes the values it receives (which may or may not be the same as what you passed or intended to pass) as a parameter and applies a specific set of business rules to them. There is no intelligence beyond applying those simple rules. If you tell me what value Round() sees, and how many digits you want it rounded to, I can give you the correct answer every time, without actually running it to verify it, since I know what those rules are. The same is true of the Banker's Rounding function.
While there are questions which have a very specific, unarguable answer, this isn't one of them, so people need to look at the evidence and make up their own minds. If he makes false claims and doesn't back them up, it's up to us to demonstrate, with evidence, why they are false. At that point, people have what they need to figure out what works for them, and won't blindly take bad advice, simply because it comes from someone who is indeed an expert in many facets of the SQL Server world.
I can't speak for anyone else, but I'm at the point where I don't believe Sergiy even believes his own claims anymore, so now we're just playing Whac-a-mole as he moves around, changing the topic each time he is shown to be incorrect. I do think we've reached the point where there is plenty of information for anyone to make up their mind, and the game is sort of boring since he doesn't actually participate in an honest manner, so I don't have a problem with moving along.
-
I was reminded this morning what started this. i.e. the business requirement.
I work for a large metal re-processing company. People fetch things into our depots, such as old stainless steel sinks, copper pipe, brass fittings and the like, and we pay out cash.
Certain depots for historical purposes round to the nearest 'unit of choice', some paying to the nearest .50 of a pound/dollar/euro, where as others round to the nearest .10. This stops us having to worry about having the right amount of 'small' change in the safe. In the UK you pay to change notes into bags of change of smaller denominations.
I was asked this morning if it was possible to change the rounding at one depot from .50 to .01 i.e. from the nearest 50 pence/cents to the nearest penny/cent.
"But of course" is the reply, it is configurable. Choose the appropiate "level" of rounding to use in the applications config and there you go, it uses it.
The use of BR in all this? If we are using to the nearest .50 (say) of the currency unit, using traditional rounding will in the long run cost us more. That's why I was asked for an implementation.
The NOT USED SQL Solution
select round (1.5650, 2) TraditionallyRounded
select round (1.5650, 2, 1) Truncated
-- These is using the first function I posted in *this* thread
select dbo.fn_Bround_1(1.5650, 2) roundToEvenHalf --nearest 50
select dbo.fn_Bround_1(1.5650, 100) roundToEvenPenny -- nearest penny
As I said, in the very first post, (
500{Edit} Sorry, 600 odd ago!) the requirement has gone away. In other words, it was resolved in the client app. I was curious as to why my first stab some times returned the 'wrong' results for certain numbers.Yes, I do want to paint my walls red!
-
Right, David, business requirement.
Let's see.
You get the amounts to pay from where?
Weight*price.
Right?
Price is written on the wall, but where do you get weight?
From digital weights.
Right?
Now, how those digital weights work?
Sensor generates some voltage proportional to the weight placed on it, and internal comparator compares it to internally generated voltage.
Internal generator increases voltage by single unit and counting steps taken. As soon as internal voltage > voltage from sensor counting is stopped.
The weight you see is number of steps taken while sensor voltage was less than internal voltage * the weight of one step.
If to assume that your step is 1g, what does it mean when the weights show 1.125 kg?
Is it precise number with all implied zeros following?
Not really. It means that your weight took 1125 steps while the real weight was less than internal "virtual" weight, and 1126th step was "over" - not to be counted.
So, the real weight is definitely > 1.125kg, you just cannot get exact match in real world.
Knowing that, how 1.125 must be rounded to 2 decimal digits?
Well, not the way BR does it.
Why your company have chosen BR for this?
Right, it works for their profits.
It's just a way to cheat on customers, to pay them less than they should get.
It's probably not most effective way of cheating, but at least it's legal.
And errors in my favour in 900 cases out of 20000 - not so bad rate.
P.S. Have you already disovered the way they cheat on you?
_____________
Code for TallyGenerator -
Sergiy,
Actually, in this case, I would round the weight (1.125 kg) to 1.13 kg. I would use the the Banker Round function in the calculation (rate * weight) to determine what to pay the customer. Also, remember the Bankers Round function actually is fairer to all customers and the business. Please look at the code and results from David M.'s tests using 100,000 (+) random values.
Again, this is a business decision that a company needs to make, not us in IT. We can only offer suggestions on how things should be done, and then implement the business decision even if we feel it is wrong. Our only other choice, is walk out the door and look for other employment.
-
Again, this is a business decision that a company needs to make, not us in IT. Yes. IT are always asked the impossible
We can only offer suggestions on how things should be done, and then implement the business decision even if we feel it is wrong wow can I relate to that
Our only other choice, is walk out the door and look for other employment Yes. Hopefully the last one, but always an option
But whatever happens or what our beliefs are, the business always wins
Far away is close at hand in the images of elsewhere.
Anon. -
So, if you would not use BR, why you insist it has to be used?
They don't round weight, they round amounts.
If price is $1.00 per kg - they pay you 1.12 despite the fact it must be 1.13.
And errors happen only in their favour, never in yours.
Please look at the code and results from David M.'s tests using 100,000 (+) random values.
His code is build and interpreted in a wrong way.
I clearly displayed that increasing precision by 1 digits reveals that BR returns errors in 900 cases out of 20k.
Again, this is a business decision that a company needs to make, not us in IT.
Did I ever discussed business decisions?
I just made a point about error inbuilt in BR concept. Nothing else.
BTW, this "business decision" is illegal here.
_____________
Code for TallyGenerator -
Sergiy,
You keep saying that the use of the Bankers Round function is illegal in NZ, that's fine. That doesn't mean it is illegal every where else. Just curious on the point of law, though, how about a citation regarding the law.
Also, if the weight, 1.125 kg, is accurate to .001, then 1) don't round the weight, take it (a business decision) as precise (the 5 is implicitly followed by zeros), 2) multiply it by the rate (n.nn also precise to the .01), resulting in a precise number: n.nnnnn followed by zeros after the 5th decimal place. Perform your businesses choice of rounding on the result.
I ran David M.'s code, and the sum of the BR values was VERY close to the sum of the actual values before rounding, while the sum of the values rounded using traditional rounding was ALWAYS much higher. Therefore, the Bankers Round function is fairer OVERALL than traditional rounding.
-
> take it (a business decision) as precise
Sorry, I prefer to take things as they are, not as they can fit somebody's poor head.
That 1.125kg does not have following zeros (neither implicit nor explicit), so I don't have any reason to take it like that.
Moreover, I have strong reasons to be sure that following digits are DEFINITELY NOT ZEROS.
So, I start from this point. Real life point.
This is right business decision.
I ran David M.'s code, and the sum of the BR values was VERY close to the sum of the actual values
He never operated with actual values.
He always used precise representations.
And he failed to produce any result from such simple number as 1/3. Because he could not find a place for it amongst his "precise values".
I prefer to work with functions which can support at least simple rational numbers.
Not to mention infinite number of irrational values.
Then, if you prefer to play with numbers, not mathematical abstractions, try this.
SUM of 3 values 1/3 expressed in decimal(8,2) notation gives you 0.99.
It's not a real value of 1/3+1/3+1/3. Error of decimal representation brings total down.
SUM of any number of decimal representations of any values is always less than SUM of actual values. Because it's a rule: any representation (before rounding) should not be bigger than the actual value.
David's example shows that BR is closer to SUM of decimal expressions, which contains negative value of total decimal representation errors.
This proves that BR inherits this error and brings average of actual values down.
_____________
Code for TallyGenerator -
Sergiy,
You have never proved your point, nor will you ever prove your point. You have consistantly gone off on tangents (really, what the heck does the Catholic Church 400 or so years ago have to do with this?); you post arguments with out any context, and then say we are wrong when we put it into a specific context by saying "that isn't what I said..", etc.
We all may as well think of this as nothing more that a tic-tac-toe game and just quit the constant back and forth pickering as neither side is going to convice the other they are wrong; it is obviously futile.
Also, I remember asking you for the legal citation showing that the use of the Bankers Round function is illegal in NZ. Are you going to provide that or do we still just have your word on it?
-
[after playing out all possible outcomes for Global Thermonuclear War]
Joshua: Greetings, Professor Falken.
Stephen Falken: Hello, Joshua.
Joshua: A strange game. The only winning move is not to play.
--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)
Intro to Tally Tables and Functions -
I see, you have nothing to say.
So, you moved again to your favourite exercise with making personal attacks.
I wonder, when your next "Good bye forever!" is gonna happen?
_____________
Code for TallyGenerator -
Sergiy,
Let see, here is what I said:
You have never proved your point, nor will you ever prove your point. You have consistantly gone off on tangents (really, what the heck does the Catholic Church 400 or so years ago have to do with this?); you post arguments with out any context, and then say we are wrong when we put it into a specific context by saying "that isn't what I said..", etc.
We all may as well think of this as nothing more that a tic-tac-toe game and just quit the constant back and forth pickering as neither side is going to convice the other they are wrong; it is obviously futile.
Also, I remember asking you for the legal citation showing that the use of the Bankers Round function is illegal in NZ. Are you going to provide that or do we still just have your word on it?
Nope, I don't see any personal attacks, just factual observations. If anyone (other than Sergiy) can show me where in the above three paragraphs there is a personal attack, please show me.
So, again, how about a straight answer for a straight question: what is the legal citation in New Zealand Law that makes use of the Bankers Round or Round to Even methodology illegal?
Viewing 15 posts - 271 through 285 (of 377 total)
You must be logged in to reply to this topic. Login to reply