Results 1 to 8 of 8

20030527, 03:04 #1
 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))))

20030527, 05:44 #2
 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.jkpads.com
Professional Office Developers Association

20030527, 06:18 #3
 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

20030527, 07:08 #4
 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))

20030531, 02:44 #5
 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

20030531, 10:18 #6
 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

20030531, 12:06 #7
 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.

20030531, 20:28 #8
 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>