CODEWITHSUNDEEP
exceldelphi
Seti Net
Seti Net

Reputation: 743

Translation of Excel INT to Delphi

I am trying to translate an Excel sheet to Delphi and stumbled over the Excel INT. Excel help say's INT rounds down (toward the negative infinity). The Excel cell is:

C13-(24*INT(C13/24))

Where C13 is initialized at -465.9862462 and calculates as 14.01375378

The best Delphi I can come up with is:

C13 := -465.9862462; 
RoundMode := GetRoundMode;// Save current mode
SetRoundMode(rmDown);
C13 := C13 - (24 * (Round(C13 / 24)));
SetRoundMode(RoundMode);// Restore mode

This produces the correct answer but is there a better way to do this?

Upvotes: 1

Views: 240

Answers (3)

Rudy Velthuis
Rudy Velthuis

Reputation: 28806

Delphi has an Int() function too. It is even documented:

http://docwiki.embarcadero.com/Libraries/Tokyo/en/System.Int

That would grossly be the equivalent of the Excel function. It also rounds down, but not towards negative infinity. It rounds down towards 0. So for negative numbers, you would have to subtract 1.0:

function ExcelInt(N: Extended): Extended;
begin
  Result := System.Int(N);
  if (Result <> N) and (N < 0.0) then
    Result := Result - 1.0;
end;

Note:

One might be inclined to use System.Math.Floor, as that rounds down towards negative infinity already, but that returns an Integer, so for large values, you might get an integer overflow. Note that in Excel, every number is a Double, so its INT() returns a Double too.

Upvotes: 5

Tom Brunberg
Tom Brunberg

Reputation: 21033

Short answer: Use System.Math.Floor() function, it is functionally the same as Excel INT() function.

From the Excel documentation for the INT() function we can read that INT(number) rounds the number argument (a real number) down to the nearest integer. Ex. INT(8.9) retuns 8, INT(-8.9) returns -9

From the Delphi documentation for the System.Math.Floor() function we can read that Floor() rounds the float type argument toward negative infinity and returns an integer. Ex. `Floor(2.8) returns 2, Floor(-2.8) returns -3

Testing your function in Excel ( C13-(24*INT(C13/24)) ) and in Delphi ( C13 - (24 * Floor(C13 / 24)) ) both yield the same result of 14.0137538

Upvotes: 2

David Heffernan
David Heffernan

Reputation: 613033

The Delphi equivalent is floor:

Rounds variables toward negative infinity.

Upvotes: 2

Related Questions