Post reply

Bankers Rounding

  • May 31, 2007 at 11:02 pm

    #710210

    I never told you're stupid.

    You took that privilege.

    I stated that whole idea of Bankers Rounding is stupid. And I can prove it.

    You defined precision by yourself. Did you forget - REAL MONEY?

    The way you prefer it's done you defined by yourself as well. See your own example:

    round(1./8, 2) + round(7./8, 2).

    You claimed bnRound(1./8, 2) + bnRound(7./8, 2) is the way to go.

    Now try to prove it on my real life example.

    Or admit you're wrong.

    Instead of putting labels on people.

    _____________
    Code for TallyGenerator

  • May 31, 2007 at 11:03 pm

    #710211

    Jeff,

    Okay, I will.  Nuff said about this.

    Lynn

    (ps you spelled my last name wrong in your edit earlier in the post)

  • May 31, 2007 at 11:05 pm

    #710212

    Can't.  Implicitly promised Jeff I wouldn't argue with you anymore in public.

  • May 31, 2007 at 11:11 pm

    #710213

    "Too much proud" problem?

    _____________
    Code for TallyGenerator

  • May 31, 2007 at 11:17 pm

    #710216

    C'mon old friend...

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

  • June 1, 2007 at 2:38 am

    #710247

    Sergiy, the point of banker's rounding isn't to be accurate, it's to be fair. The assumption behind it is that if you round 1.235 up to 1.24 every time, that half cent goes to the same party every time, giving them a monetary advantage. That's why 1.235 is rounded up to 1.24, while .1225 is rounded down to 1.22. Each party should have the rounding advantage in their favor approximately half the time, assuming equivalent transaction counts in each direction.

    While you might think it's silly or inaccurate, fairness is its only goal.

     

  • June 1, 2007 at 2:54 am

    #710250

    Interesting that bankers sometimes get to be fair..."...the point of banker's rounding isn't to be accurate, it's to be fair"...normally we're accused of far worse crimes.  (Though I suspect it was an effort to prevent us being the disadvantaged party!!!)

  • June 1, 2007 at 4:05 am

    #710266

    Just walked back into work and i was overwhelmed by the number of responses sat in my mailbox

    First of all, many thanks to all who have contributed.

    Antares686's solution did indeed work for me for the test cases provided, many thanks.

    Lynn is clearly a far better mathematician than I, and his function works as specified.  I just don't understand how!

    Jeff, you are right, I went to wikipedia for the definition.  I did give them credit though

    Sergiy, ignoring the validity of 'bankers rounding', I just wanted to know why the original algorithm worked only sometimes. You wrote

      Here is the mistake:

      ... Floor(Abs(@Temp + 0.5 * Sign(@p1)))

    What is the solution if it differs from using different data types as per Lynn's/Antares686's solution?

    For what it is worth, we maintain a wiki here at work.  Some one posted the old but funny "how to shoot your foot off".  For those who are not familiar with it, here are the first two entries:

    Shoot Your Foot Off

    The Programmer's Quick Guide to Languages

    The proliferation of modern programming languages (all of which seem to have stolen countless features from one another) sometimes makes it difficult to remember what language you're currently using. This handy reference is offered as a public service to help programmers who find themselves in such a dilemma.

    TASK: Shoot yourself in the foot.

    • C:

      • You shoot yourself in the foot.

    • C++:

      • You accidentally create a dozen instances of yourself and shoot them all in the foot. Providing emergency medical assistance is impossible since you can't tell which are bitwise copies and which are just pointing at others and saying, "That's me, over there."

    We started appending to this list as a gentle way of ribbing each other.  Here is my entry.  That is, written about me, not by me.

  • Jacko Methodology

    • You search the web and in 15 seconds find someone else's foot-shooting code, you download it, it works, you go to the pub, come back and it doesn't work - as you didn't write it in the first place you now don't know whether it's you or the beer.....

    Remember, your sense of humour is your defence against the rigours of life...

    • Cheers

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • June 1, 2007 at 4:16 am

    #710268

    David, makea table, populate 1st column witn money numbers from 0.000 to 0.1999 step 0.0001.

    This will cover both cases of Banker Rounding.

    Populate 2nd column with bnRound result from Col1.

    Count number of values rounded up (Col2 > Col1) and number of values rounded down (Col2 < Col1).

    You'll see it's neither accurate nor fair.

    Banker Rounding works only when you distribute a value over 2 lines.

    It does not help in any way for 3 lines. And it possibly makes it worse for 4 or more lines.

    I gave you example of New Zealand invoice having 8 lines with odd Net Amounts. Banker Rounding brings you here 4 cents out of tax total.

    Not quite according to the legal requirements.

    Believe me, it's not most unfortunate situation. Add distribution of each line over 5 or 6 cost centers - and you get the picture.

    That's where brilliant software built on something like Bankers Rounding fails and the boring academic guy comes to fix it and save the company from Revenue Department charges (before him several people had to go every day through every bloody Invoice Report and manually correct odd numbers).

    _____________
    Code for TallyGenerator

  • June 1, 2007 at 4:20 am

    #710269

    Sergiy, as i said, ignoring the validity of 'bankers rounding', I just wanted to know why the original algorithm worked only sometimes. You wrote

      Here is the mistake:

      ... Floor(Abs(@Temp + 0.5 * Sign(@p1)))  

    What's the correction?  As I've stated, I'm no mathematician.

    Cheers

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • June 1, 2007 at 4:25 am

    #710270

    David, (another one this time )

    That's what I was trying to do from the beginning:

    because I'm not familiar with any programming language I was trying to prove there is no point to shoot your foot off.

    Life with 2 feet is not so misarable, mates!

    _____________
    Code for TallyGenerator

  • June 1, 2007 at 4:41 am

    #710276

    Oops, I thought your previous post was to me.  Having said that, can you tell me what is wrong with

    Floor(Abs(@Temp + 0.5 * Sign(@p1)))

    Preferably in non-math language

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • June 1, 2007 at 5:45 am

    #710294

    I answered to Jeff already.

    _____________
    Code for TallyGenerator

  • June 1, 2007 at 7:02 am

    #710317

    Good initial post, and a good puzzle to sit down with - but I hate to see the noise level rising in the discussion. Fair enough to make sure someone understands that they may be solving a problem that is ill defined, but after that I hope we can then just solve the problem anyway, always something to be learned.

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • June 1, 2007 at 7:42 am

    #710332

    David,

    Here's an attempt to explain the "errors" that creep in... same "error" bit me big time... Lynn pointed it out to me and Serqiy gave me the tool that explained "why"... so, without getting into a "holy war" about "precision", what would you expect the following to give?

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

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

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

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

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

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

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

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

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

    select 612.095, str(612.095, 10, 2)

    That's right... you'd expect each number to be rounded up to the nearest hundreth... you'd expect that 612.005 would round up to 612.01 and you'd expect that 612.015 would round up to 612.02... BUT IT DOESN'T... it rounds DOWN to 612.01... is it an error... it is on OUR part because we didn't consider what's going on in the background with FLOAT...

    STR implicitly does a conversion to FLOAT (I forgot about that)... and the value for 612.015 as a FLOAT will take some by surprise... if you run the following, you'll see why 612.015 rounded down instead of up like we expected...

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

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

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

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

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

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

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

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

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

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

    -------- ----------------------------------------------------- ----------

    612.005  612.005                                                   612.01

    612.015  612.01499999999999                                        612.01

    612.025  612.02499999999998                                        612.02

    612.035  612.03499999999997                                        612.03

    612.045  612.04499999999996                                        612.04

    612.055  612.05499999999995                                        612.05

    612.065  612.06500000000005                                        612.07

    612.075  612.07500000000005                                        612.08

    612.085  612.08500000000004                                        612.09

    612.095  612.09500000000003                                        612.10

    This happens because within the range of the BINARY registers within SQL, there is no equivelent representation for 612.015 in binary (FLOAT is binary math).

    Bottom line is, when you work with FLOAT, you really need to know what's going on in the background.  To coin a phrase, although excellent for scientific calculations, FLOAT is a "WYSINWYG" data type ("What you see is NOT what you get")   If you don't do the necessary "display" rounding before you use it's value, you will sometimes come up with the "wrong" answer (or at least one you weren't expecting) just as your function sometimes does because you use FLOAT for the parameters...

    Sure hope that helps...

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

    • Viewing 15 posts - 31 through 45 (of 373 total)

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