Bankers Rounding

Question

Post reply

Bankers Rounding

Viewing 15 posts - 16 through 30 (of 373 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110206 More actions · Answer 1

If you run

select round(1./3,2) + round(1./3,2) + round(1./3,2)

result will be 0.99.

No matter which rounding you gonna use.

And re-read initial post:

When dealing with large sets of scientific or statistical data, where trends

are important, traditional rounding on average biases the data upwards slightly.

Someone who wrote this definitely missed couple of important things in school.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Sergiy,

You are missing the point, there is a use for the bankers round. You are making things more difficult than they really need to be.

Jeff,

I'll take that as your excuse, but that is how it works in 2K5 SP1 and SP2.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I reread his post, but not withstanding,, I have never used a bankers round in any statistical or scientific calculation. I have used it in accounting and tax applications.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 4

Yeah... and it get's worse... check it out... didn't use to work this way...

select str(612.005, 10, 2) UNION ALL

select str(612.015, 10, 2) UNION ALL

select str(612.025, 10, 2) UNION ALL

select str(612.035, 10, 2) UNION ALL

select str(612.045, 10, 2) UNION ALL

select str(612.055, 10, 2) UNION ALL

select str(612.065, 10, 2) UNION ALL

select str(612.075, 10, 2) UNION ALL

select str(612.085, 10, 2) UNION ALL

select str(612.095, 10, 2)

select str(3.005, 10, 2) UNION ALL

select str(3.015, 10, 2) UNION ALL

select str(3.025, 10, 2) UNION ALL

select str(3.035, 10, 2) UNION ALL

select str(3.045, 10, 2) UNION ALL

select str(3.055, 10, 2) UNION ALL

select str(3.065, 10, 2) UNION ALL

select str(3.075, 10, 2) UNION ALL

select str(3.085, 10, 2) UNION ALL

select str(3.095, 10, 2)

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

Sergiy SSC Guru Points: 110206 More actions · Answer 5

And for accounting and tax applications you must use accounting and tax data type: money, 4 digits after comma.

Do presentation rounding AFTER last calculation is completed, as school math handbook prescribed:

select ROUND(round(1./8,4) + round(7./8,4) , 2)

Result - 1.00. No rounding errors.

No freaky rounding required.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Sergiy,

You are definately a Type A personality.

Yes, the money type goes to 4 decimal places, but unfortunately, REAL MONEY doesn't, so going to 4 decimal places doesn't help when the smallest value you can collect is a penny (0.01). Therefore there is an application for the bankers round. You just need to take it on faith and stop trying to have to be right all the time. It isn't going to happen.

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Jeff, don't blame SP4.

Blame yourself.

For implicit conversions you allow when you pass decimal number to float parameter.

Run this and relax:

select convert(float, 612.005), str(612.005, 10, 2) UNION ALL

select convert(float, 612.015), str(612.015, 10, 2) UNION ALL

select convert(float, 612.025), str(612.025, 10, 2) UNION ALL

select convert(float, 612.035), str(612.035, 10, 2) UNION ALL

select convert(float, 612.045), str(612.045, 10, 2) UNION ALL

select convert(float, 612.055), str(612.055, 10, 2) UNION ALL

select convert(float, 612.065), str(612.065, 10, 2) UNION ALL

select convert(float, 612.075), str(612.075, 10, 2) UNION ALL

select convert(float, 612.085), str(612.085, 10, 2) UNION ALL

select convert(float, 612.095), str(612.095, 10, 2)

select convert(float, 3.005), str(3.005, 10, 2) UNION ALL

select convert(float, 3.015), str(3.015, 10, 2) UNION ALL

select convert(float, 3.025), str(3.025, 10, 2) UNION ALL

select convert(float, 3.035), str(3.035, 10, 2) UNION ALL

select convert(float, 3.045), str(3.045, 10, 2) UNION ALL

select convert(float, 3.055), str(3.055, 10, 2) UNION ALL

select convert(float, 3.065), str(3.065, 10, 2) UNION ALL

select convert(float, 3.075), str(3.075, 10, 2) UNION ALL

select convert(float, 3.085), str(3.085, 10, 2) UNION ALL

select convert(float, 3.095), str(3.095, 10, 2)

STR does its job perfectly. According to the data you supply.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 8

Cool!

I'm now classified!

REAL MONEY appears in final report, after FINAL rounding.

The rule from math handbook:

Convert your "real money" to money BEFORE you start any calculations, round final money result to your "real money" after all calculations are complete.

No problems with that approach.

Just follow those bloody academic rules.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I think you will finally understand when pigs fly. You just don't seem to get it and I obviously don't have what it takes to convince you otherwise. Like I said, obviously you MUST ALWAYS BE RIGHT. So I will be the better man, and admit when I am wrong, because obviously YOU NEVER WILL.

Sergiy SSC Guru Points: 110206 More actions · Answer 10

Lynn, I'm not always right, but here I'm absolutely right.

Try calculate 1./8 + 1./8 using your stupid rounding and tell me does it bring you right result.

Because my way brings it.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110206 More actions · Answer 11

Lynn, I will admit anything you can prove.

Can you?

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Unfortunately, I don't have access to application where I had to use the bankers round. It is my previous employers in-house software, written in COBOL using ISAM databases with all money fields defined with 2 decimal places.

To get the tax breakdown calculations to come out right, using the bankers round was the only way to do it consistantly and meet the auditors requirements.

Good enough or do I need to see if one of my former co-workers there can send me that part of the code to so you?

Sergiy SSC Guru Points: 110206 More actions · Answer 13

I don't need that code.

We've got here GST rate = 12.5%. It's right from your exaple: 1./8

Now just show me how your Bankers Rounding could help me to get right amount of tax from 10 items $1 each:

10 * bnRound($1./8) = bnRound($10./8)

Can you make it?

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Unfortunately, you didn't tell me to what precision you wanted the values rounded or if I am to round the total or each value first. Stats prof taught me that figures never lie but liars figure.

No matter how I may perform the calculation you ask, you will still find a way to disprove my point that there can be a need for a bankers round function, right?

Also, I am not stupid nor is my function. I am a highly educated and experienced IT professional.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 15

Crud... there goes the neighborhood Just kiddin' guys... but cut it out, please.

Serqiy, ran the query you posted you were absolutely correct on the STR thing... and I mistook it way back when for doing Bankers' Rounding

I'll be back right after I make some corrections to that other thread... don't want anyone to have bad info on my account...

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