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

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