PDA

View Full Version : Simulate Excel's Ceiling function in Delphi?

paul_nicholls
23-02-2011, 11:24 PM
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
24-02-2011, 01:38 AM
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
24-02-2011, 02:24 AM
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
24-02-2011, 03:03 AM
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
24-02-2011, 03:55 AM
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