# Thread: Translating a Lotus formula (Smart Suite 97)

1. ## 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. ## 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?

3. ## 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. ## 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. ## 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. ## Re: Translating a Lotus formula (Smart Suite 97)

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

Steve

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