Sunday, June 10, 2012

Rounding the Currency type like they taught us in school

I started using Delphi's Currency type for it's accuracy only to discover it doesn't round the way I would have expected it to round. I remember being taught in school that when rounding numbers if the remainder was equal to or greater than five you round up otherwise you round down.

For Example:
  • 12.344 Rounds to 12.34
  • 12.345 Rounds to 12.35
  • 12.346 Rounds to 12.35
This is how I was taught in school.
This is how Microsoft Excel does it when you set the cell to display as two decimal places.
This is how Microsoft SQL does it when you format the display as two decimal places.

'---------------------------------
' Microsoft SQL
'---------------------------------
DECLARE @Money money
SET @Money = 12.345
SELECT CONVERT (varchar(10),@Money,1)
'---------------------------------
12.35
But that's not how Delphi does it.

'---------------------------------
' Delphi 2010
'---------------------------------
procedure TForm1.Button1Click(Sender: TObject);
var
  s : string;
  c : currency;
begin
  c := 12.345;
  s := '';
  s := s + 'Value ' + FloatToStr(c);
  s := s + Chr(13);
  s := s + Format('Formatted as money = %m',[c]);
  ShowMessage(s);
end;


So this lead me looking around for an answer. It appears that Delphi uses what is known as Bankers Rounding which means round to the closest even number. I was never taught this in school. This is how Bankers Rounding works:
  • 12.345 Rounds to 12.34
  • 12.355 Rounds to 12.36
  • 12.365 Rounds to 12.36
I do not want this type of rounding behavior... I want the rounding that I was taught in school. The problem is Delphi's internals do not support this type of rounding. (This is where I have one of those love hate realtionships with Delphi. I hate Delphi!)

So I posed a question on StackOverflow and after clarifying what I meant, got a couple interesting responses. Here is a solution I found reading through the Embarcadero discussion mentioned in my question on SO.
function RoundCurrency(const Value: Currency): Currency;
var
  V64: Int64 absolute Result;
  Decimals: Integer;
begin
  Result := Value;
  Decimals := V64 mod 100;
  Dec(V64, Decimals);
  case Decimals of
    -99 .. -50 : Dec(V64, 100);
    50 .. 99 : Inc(V64, 100);
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  s : string;
  c : currency;
begin
  c := 12.345;
  s := '';
  s := s + 'Value ' + FloatToStr(c);
  s := s + Chr(13);
  s := s + Format('Formatted as money = %m',[c]);
  s := s + Chr(13);
  s := s + Chr(13);
  s := s + 'Using the RoundCurrency function';
  s := s + Chr(13);
  s := s + Format('Formatted as money = %m',[RoundCurrency(c)]);
  ShowMessage(s);
end;

Now that's more like it. I love Delphi!

Enjoy
Semper Fi - Gunny Mike

3 comments:

  1. Take a look at John Herbster's rounding routines at http://cc.embarcadero.com/item/21909. His rounding function offers the following options as one of the parameters:

    (drNone, {No rounding.}
    drHalfEven,{Round to nearest or to even whole number. (a.k.a Bankers) }
    drHalfPos, {Round to nearest or toward positive.}
    drHalfNeg, {Round to nearest or toward negative.}
    drHalfDown,{Round to nearest or toward zero.}
    drHalfUp, {Round to nearest or away from zero.}
    drRndNeg, {Round toward negative. (a.k.a. Floor) }
    drRndPos, {Round toward positive. (a.k.a. Ceil ) }
    drRndDown, {Round toward zero. (a.k.a. Trunc) }
    drRndUp); {Round away from zero.}

    "Grade school" rounding would use drHalfUp. See also the Wikipedia article on rounding. It explains pros and cons of various rounding methods.

    Another old fart,
    Max Williams

    ReplyDelete
  2. Rounding is a nest of rattle snakes, school rounding looks good, but is in practice not a good practice. Bankers rounding does not look good, but makes an awful lot more sense.
    Really there, blame the school, not Delphi.
    And Excel only "cheats" the rounding for appearances, when chaining it uses bankers rounding (leading to odd calculus vs display oddities)

    ReplyDelete
  3. Hi!

    Maybe i have a workaround:

    procedure TForm1.BitBtn1Click(Sender: TObject);
    var
    C: Currency;
    begin
    C := 12.345;
    SetRoundMode(rmNearest);
    ShowMessage(FloatToStr(RoundTo(C, -2)));
    end;

    Rolphy Reyes

    ReplyDelete