Bankers' Rounding Custom Function

[DG]

Here is a first attempt at a CF to round a value to a given precision using bankers' rounding.

//BRound( value; precision )

//By Simon Joyner

//26/01/2006

//Returns  rounded to  using Bankers' Rounding. Accepts negative precisions and negative values.



Let([

    polarity = Sign(value);

    decimalPos = Position( Abs(value); "."; 1; 1 );

    fullNum = Filter ( value ; "1234567890" );

    leftNum = Left( fullNum; (decimalPos - 1) + precision);

    rounder = Middle( fullNum; (decimalPos + precision); 1);

    lastNum = Right(leftNum; 1);

    leftRounded = Case( 

                      rounder > 5 or (rounder = 5 and Mod(lastNum; 2) ≠ 0);

                      leftNum + 1;

                      leftNum

                      );

    power = Length(Int(Abs(value))) - Length(leftNum)

];

GetAsNumber(

    Case( polarity < 0; "-") &

    Case( power < 0;

        Left( leftRounded; decimalPos - 1 ) & "." & Middle( leftRounded; decimalPos; precision);

        leftRounded & "e+" & power

        )

)//GetAsNumber

)//Let

All comments, improvements, optimising etc. greatly apprectiated.

[Ender]

Not being a banker, I'm not sure what the significance of this is. Can you give some examples?

[DG]

Sure, no problem.

The Round function provided with FM rounds up at 5's and higher.

This means that if you average a lot of values which end in 5 such as prices ($1.95 etc.) the result will be biased upwards.

To get a "fairer" average, banks sometimes use Bankers' Rounding which rounds to the nearest even value.

Examples:

Value       Normal         Bankers

 0.5               1                     0

 1.5               2                     2

 2.5               3                     2

 3.5               4                     4

Hope that made sense...

[Lee Smith]

Try running your theory by the Truth In Lending Laws.

Bankers DO NOT ROUND beyond the nearest penny

1.3333333333333333

is

1.33

1.666666666666666

is

1.67

Lee

Edited January 25, 2006 by Guest
oops forgot the last part

[DG]

Wow, great resource Lee, thanks.

The function as it is at the moment returns the values as you have listed as long as the precision is set to 2.

I think I'm missing your point, could you elaborate?

Thanks

[DG]

Ok, I think I see - maybe the $1.95 was a bad value to use as it would never be rounded.

1.945 is a better starting value.

Wirh the FM round function, this ends ups as 1.95.

With bankers it would be 1.94.

[Lee Smith]

You are only working the the decimals beyond the 2 places.

If all of the decimals beyond those 2 places are equal to, or more than a 1/2 cent, then it would round up, if not, it rounds down, or essentially remaining unchanged.

Lee

[DG]

So an example that the function gets wrong is:

1.1456

This should give 1.15 because everything to the right of the 2nd decimal taken as a whole is greater than 5.

It currently gives 1.14 which is incorrect.

Is that right?

[comment]

I happen to know what "Banker's rounding" is*. Still, I have no idea what you're trying to do in that calculation. What's worse, it's not returning correct results: a value of 1.5, rounded to precision of 0, should return 2. Yours returns 20.

IMHO, this is a purely numerical problem, and can be solved without resorting to text manipulations:

Let ( [

rem = Mod ( number * 10^precision ; 1 ) ;

prev = Mod ( Int ( number * 10^precision ) ; 10 )

] ;

Case (

rem = 0.5 and not Mod ( prev ; 2 ) ;

Truncate ( number ; precision ) ;

Round ( number ; precision )

)

)

(*) http://en.wikipedia.org/wiki/Significance_arithmetic#The_round-to-even_rounding_rule

[DG]

Excellent, thanks for that.

[SlimJim]

If you always round up then you can introduce rounding errors. For example 1.5 cents rounds up to 2 cents, 2.5 cents rounds up to 3 cents. Add the two together and you find 4 cents becoming 5 cents. Rounding error can be reduced by leaving rounding until you have completed all other operations but the practice of always rounding up at .5 leads to inevitable errors.

To reduce the effect of this simple rounding error you need to round up half the time and round down half the time and the even/odd choice is the common way of doing that. Note that it does not remove the errors it balances the effect.

[Lee Smith]

I don't agree with you Jim. However, what do I know? I was only a banker for 35 years.

From my experience each account is separate, therefore the round up and down evens out.

Lee

[DG]

Now that I've done some proper digging (which I admit might have been an idea before having a stab at the cf : ) , there are long discussions and disagreements on the web about the sta tistical benefits of round up versus round even (aka Bankers').

However, the accounting package I am linking to from FM uses round even so I have to too.

Thanks again for all help and comments.