Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Translating a Lotus formula (Smart Suite 97)

    Here is a formula that I have used in lotus to project the end of a 28 day time cycle, and I have not been able to comvert this to an excel formula. Can any on help me with this!! Here is a the formula that I used in Lotus! @IF($A20<1,EMPTY,@SUM($A20+(@ROUNDM(@SUM(@NOW-$A20),28,1))))

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Translating a Lotus formula (Smart Suite 97)

    Excel doesn't have an EMPTY function (alas). Replace the EMPTY with two double quotes (indicating an empty string) : ""

    Also, I have no clue what the ROUNDM in Lotus does, could you elaborate so I can find an alternative function?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Translating a Lotus formula (Smart Suite 97)

    I found a reference to @roundm. It seems to be MODAL rounding (like rounding $1.65 to a buck and three quarters). A straight "translation" of the formula should then look like this:

    <font face="Georgia">=IF(A20<1,"",SUM(A20+ROUND(SUM(NOW()-A20)/28,0)*28))</font face=georgia>

    Where the <font face="Georgia">ROUND(SUM(NOW()-A20)/28,0)*28</font face=georgia> replaces that<font face="Georgia"> @ROUNDM( <img src=/w3timages/censored.gif alt=censored border=0> ,28,1)</font face=georgia> business. And Jan's empty quotes ("") for Lotus' <font face="Georgia">EMPTY</font face=georgia>

    Errol

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Translating a Lotus formula (Smart Suite 97)

    It would seem then that the Lotus function @ROUNDM is more or less equivalent to MROUND in the Analysis ToolPak: MROUND(SUM(NOW()-A20),28) rounds to the nearest multiple of 28.
    In Excel, the SUM can be omitted, SUM(NOW()-A20) is equivalent to NOW()-A20 and SUM(A20+ROUND(...)) is equivalent to A20+MROUND(NOW()-A20,28). So the entire formula becomes

    =IF(A20<1,"",A20+MROUND(NOW()-A20,28))

  5. #5
    New Lounger
    Join Date
    May 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Translating a Lotus formula (Smart Suite 97)

    Hans, Sorry it took me so long to reply! This formula is close to what I need however MROUND rounds up or down to the closest multiple. I need it to round to the next higher multiple. Ken

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Translating a Lotus formula (Smart Suite 97)

    Have you tried adding 0.5 to the number before using Mround?

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Translating a Lotus formula (Smart Suite 97)

    The CEILING function rounds to the next higher multiple, so try CEILING(NOW()-A20,28) instead of MROUND(NOW()-A20,28) in the formula.

    If you look up one of these functions in the online help, and then click "See Also", you'll see a list of the available rounding functions.

  8. #8
    New Lounger
    Join Date
    May 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Translating a Lotus formula (Smart Suite 97)

    Hans,
    This was exactly the formula that I was looking for! I put it in the IF formula that you gave me before and it worked perfectly.
    Thanks, Ken <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

Posting Permissions

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