Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get Delphi Currency Type to Round like Excel all the time?

I'm trying to get Delphi to Round like Excel but I can't. Here is the code:

procedure TForm1.Button1Click(Sender: TObject);
var
 s : string;
 c : currency;
begin
 c := 54321.245;
 s := '';
 s := s + Format('Variable: %m',[c]);
 s := s + chr(13);
 s := s + Format('   Literal: %m',[54321.245]);
 ShowMessage(s);
end;

Delphi Rounding

I'm using a currency variable that is set to 54321.245 and when I format this variable it rounds using Bankers Rounding. However, when I format the same value as a literal it rounds the way that Excel rounds.

I was expecting this to round to $54,321.25 whether it's formating a currency variable or a literal value. How can I make sure that Delphi rounds the same way as Excel every time?

Edit

The rounding I expect to see is as follows:  
54,321.245   = 54,321.25  
54,321.2449  = 54,321.24  
54,431.2499  = 54,421.25 

I am only using literals to show the different ways Delphi rounds. I expect to use variables in the actual code.

Note:
If I change the variable from currency to extended it rounds correctly

Edit #2

Some have suggested that I do not have a clear understanding of my requirements, this is absolutely not true. I have a very clear understanding of my requirements, I'm obviously not doing a very good job of explaining them. The rounding method I want is two decimal places. When the deimal part has a thousandths value >= 0.005 I want it rounded to 0.01 the currency type offered by Delphi does not do this. I also tried this example using Microsoft SQL with a money datatype (which I assumed was the same as Delphi's currency) and SQL rounds it's money type the way I described.

  • SQL Money >= 0.005 = 0.01
  • Delphi Currency >= 0.005 := 0.00

Edit #3
Good Article: http://rvelthuis.de/articles/articles-floats.html
Possible Solution: http://rvelthuis.de/programs/decimals.html

Edit #4
Here is one of the solutions from the Embarcadero discussion

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;
like image 258
Michael Riley - AKA Gunny Avatar asked Jun 10 '12 01:06

Michael Riley - AKA Gunny


3 Answers

If I understand you correctly, you are looking for this:

function RoundTo2dp(Value: Currency): Currency;
begin
  Result := Trunc(Value*100+IfThen(Value>0, 0.5, -0.5))/100;
end;
like image 185
David Heffernan Avatar answered Nov 14 '22 00:11

David Heffernan


It's not possible to make RTL to round the way you want. The way to affect the rounding in Delphi is to use SetRoundMode which sets the FPU conrol word for rounding, however, as far as I can tell, there's no FPU support for rounding the exact in-between to upwards (which is generally avoided because it generates a bias for higher values).

You have to implement your own rounding function. There's an extended discussion in Delphi Rounding thread on Embarcadero forums, which includes several solutions.

like image 21
Sertac Akyuz Avatar answered Nov 13 '22 23:11

Sertac Akyuz


use function System.Math.SimpleRoundTo

like image 45
Ravaut123 Avatar answered Nov 13 '22 23:11

Ravaut123