Simulate Excel's Ceiling function in Delphi?

[paul_nicholls]

Hey all,
I am trying to simulate Excel's ceiling function but I am not having much luck...

This is what result Excel gets (from the help file):

Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

Formula Description (Result)
=CEILING(2.5, 1) Rounds 2.5 up to nearest multiple of 1 (3)
=CEILING(-2.5, -2) Rounds -2.5 up to nearest multiple of -2 (-4)
=CEILING(-2.5, 2) Returns an error, because -2.5 and 2 have different signs (#NUM!)
=CEILING(1.5, 0.1) Rounds 1.5 up to the nearest multiple of 0.1 (1.5)
=CEILING(0.234, 0.01) Rounds 0.234 up to the nearest multiple of 0.01 (0.24) 

I found here (http://www.tek-tips.com/faqs.cfm?fid=5031) a Ceiling function, but I am having trouble converting it...

VB Code:

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function

Here is my Delphi Code translation (returns 0 if error):

Function ExcelCeiling2(x,factor: Extended): Extended;
// source: http://www.tek-tips.com/faqs.cfm?fid=5031
Var
  t: Extended;
Begin
    If (Abs(factor) < 0.0001) Or (Sign(x) <> Sign(factor)) Then
    Begin
        Result := 0;
        Exit;
    End;

    t := x / factor;
    If Trunc(x / factor) > 0 Then
      t := t - Trunc(x / factor);

    Result := (Trunc(x / factor) - t) * factor;
End;

Any ideas?
There is something wrong with my translation as I don't get anywhere the same results as Excel

cheers,
Paul

[LP]

The majority of the rounding can be done via Delphi's "Format" function (it doesn't handle the sign check, but you can do this separately).

It has slightly different format of the second parameter, but you can use something like:

Text:= Format('%1.4f', [2.345699]); // rounds to 2.3457

You can check the documentation for the format function - it has more options for specifying floating point numbers, perhaps you can adapt several variations of this function to simulate the Excel's one.

[paul_nicholls]

Hi Lifepower, thanks for the info

I don't suppose you could still help with the code conversion?

I am having trouble converting the Visual Basic code below to Pascal; I don't know if my translation is correct or not.

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function

The bit that is throwing me off is this

Int(X / Factor) > 0

.

cheers,
Paul

[LP]

The bit that is throwing me off is this

Int(X / Factor) > 0

I suppose it is either 1 or 0 depending on the outcome. Direct conversion would probably be this:

Result:= (Trunc(X / Factor) - (X / Factor - Ord(Trunc(X / Factor) > 0))) * Factor;

[paul_nicholls]

Thanks Lifepower

Unfortunately I have since discovered that that whole routine I was converting seems to give incorrect values anyway!

I have found that this:

Result := Round(n / s + 0.4) * s

Seems to give me accurate results

cheers,
Paul