Results 1 to 5 of 5

Thread: Simulate Excel's Ceiling function in Delphi?

  1. #1

    Simulate Excel's Ceiling function in Delphi?

    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
    Games:
    Seafox


    Pages:
    Syntax Error Software itch.io page

    Online Chess
    http://gameknot.com/#paul_nicholls

  2. #2
    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:

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

  3. #3
    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.

    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
    The bit that is throwing me off is this
    Code:
    Int(X / Factor) > 0
    .

    cheers,
    Paul
    Last edited by paul_nicholls; 24-02-2011 at 03:31 AM.
    Games:
    Seafox


    Pages:
    Syntax Error Software itch.io page

    Online Chess
    http://gameknot.com/#paul_nicholls

  4. #4
    Quote Originally Posted by paul_nicholls View Post
    The bit that is throwing me off is this
    Code:
    Int(X / Factor) > 0
    I suppose it is either 1 or 0 depending on the outcome. Direct conversion would probably be this:
    Code:
    Result:= (Trunc(X / Factor) - (X / Factor - Ord(Trunc(X / Factor) > 0))) * Factor;

  5. #5
    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:

    Code:
    Result := Round(n / s + 0.4) * s
    Seems to give me accurate results

    cheers,
    Paul
    Games:
    Seafox


    Pages:
    Syntax Error Software itch.io page

    Online Chess
    http://gameknot.com/#paul_nicholls

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •