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.
Code:
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:
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):
Code:
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
Bookmarks