migrate from Lotus 123 - roundup doesn't convert

[willstevenson]

I am trying to migrate Lotus 123 (.WK4) files to Excel 2000.
Macros have been rewritten in VBA, nested (if) statements rewritten s
as not to be > 7 levels, but the one simple function that doesn't see
to readily migrate is @ROUNDUP.
Copying and pasting the function out of 123 and into Excel and it work
fine.
E.g. @ROUNDUP(1.5, 0) returns 2 in 123
ROUNDUP(1.5, 0) returns 2 in Excel.
However opening a .WK4 file with this function in Excel gives 'Canno
read record' error and, on clicking OK, cell comments 'Formula faile
to convert'.

So...
Am I missing something simple?
And/or would it open ok in a later version of Excel?

Thanks in advance..

 

[Harlan Grove]

I am trying to migrate Lotus 123 (.WK4) files to Excel 2000. ....
E.g. @ROUNDUP(1.5, 0) returns 2 in 123
ROUNDUP(1.5, 0) returns 2 in Excel.
However opening a .WK4 file with this function in Excel gives 'Cannot
read record' error and, on clicking OK, cell comments 'Formula failed
to convert'.

....

This is just one of those things. If you still have 123, use it to save as
..XLS files rather than using Excel to open .WK4 files. When it comes to
converting formulas, my own experience is that Lotus did a better job
converting to .XLS converted than Microsoft did converting from .WK4.

 

[willstevenson]

Thanks for the response...

Further info (after investigation) ...

Doing things the other way round - i.e. opening the .WK4 file in Lotu
123 and saving as an Excel 97 .XLS file results in an error i
@ROUNDUP(1.5, 0) is used but saves ok if @ROUNDUP(1.5, 0, 1) is used
i.e. if the 3rd parameter is specified as a 1 (and not missed, o
specified as the default value of 0) then the function can be saved a
an Excel file in 123 and then opened in Excel.
(Presumably this is due to the fact that 123 and Excel roundup negativ
numbers differently - specifying 1 as the 3rd parameter makes 12
behave like Excel so it translates ok.)

HOWEVER... much as this is useful information it doesn't really solv
my problem. Specifying the 3rd parameter, and saving the file as a .WK
file and opening in Excel still doesn't work, and I need to do it tha
way round (i.e. save in Lotus 123 as a .WK4 file and open in Excel) a
there are several other functions and formatting that work this wa
round that don't work by saving in Lotus 123 as a .XLS and opening i
Excel.

@ROUND / =ROUND translates ok either way, but unfortunately this isn'
what I want - I want 1.3 to roundup to 2 etc. (i.e. I want to use th
roundup function in it's basic form - if a number is a whole numbe
leave it alone, if it's not whole, round it up to the next whol
number.)
I can't use a work around such as adding 0.5 and using ROUND as thi
would work for every fractional number but would round up whole number
to the next whole number. (And it seems wrong doing it like this anywa
as the roundup function works correctly in Lotus 123 and in Excel, i
just won't translate across!)

Any other ideas appreciated...

 

[Harlan Grove]

HOWEVER... much as this is useful information it doesn't really solve
my problem. Specifying the 3rd parameter, and saving the file as a .WK4
file and opening in Excel still doesn't work, and I need to do it that
way round (i.e. save in Lotus 123 as a .WK4 file and open in Excel) as
there are several other functions and formatting that work this way
round that don't work by saving in Lotus 123 as a .XLS and opening in
Excel.

....

All I can suggest is writing a macro in 123 to convert all cells containing
@ROUNDUP to labels by adding a single quote as the first chatacter in the
cell's formula. I believe you can use

{CONTENTS @CELLPOINTER("Address");destination;117;255}

to enter the current cell's formula in the destination cell as a label. The
last two arguments are format and width (or it could be width then format, I
don't recall for sure, and I don't have 123 on this PC). Width should be the
maximum 255 and format must be 117. Then use

{if #NOT#@ISERR(@FIND("@ROUNDUP(",destination,0))}{edit}{home}'~

to check if the current cell's formula contains @ROUNDUP(, and if so, to
convert the current cell's formula to a label. Labels convert as-is into
Excel, and you should be able to edit the formulas into shape there.

An alternative is replacing @ROUNDUP calls with @ROUND enclosed in several
unnecessary parentheses, then look for these redundantly parenthesized ROUND
calls in Excel.

 

[willstevenson]

Again, thanks for the feedback.
Have taken a macro approach similar to that suggested, but with all th
work done in Excel...(much easier)...

1. Save file in Lotus123 as .xls file (this converts ROUNDUP ok bu
doesn't convert some other functions / formatting)

2. Open .wk4 file in Excel and save as .xls file (with a differen
name).

3. Create a new separate workbook which does the following:
Open 2 workbooks (as created in steps 1 and 2).
Loop through all cells on all sheets of workbook1, check the formul
for each cell, if it contains ROUNDUP then copy it across to th
corresponding cell in workbook2. Delete the comments from the cell.

So...
All formatting is preserved as only the formula is copied.
Other workbooks may be processed in the same way by just changing th
workbook parameters in the macro.
Nothing has to be removed from the final workbook as the macro tha
does the work resides in a separate workbook.
None of the formulas that contain ROUNDUP also contain functions tha
Lotus123 couldn't convert which would have created a problem.
None of the cells with ROUNDUP formula had other comments, so deletin
the 'Formula failed to convert' comments didn't remove any othe
comments. Guess the code could be changed to check the comments firs
in workbook 1 and/or workbook 2 before doing the delete

 

[Harlan Grove]

Good that you've found a way to handle this. Please let us know which functions
123 itself couldn't convert when saving as .XLS files.