Apache OpenOffice Community Forum

Hello all:

I've recently attempted to import an excel template into OpenOffice and it looks fine but there seems to be a slight problem with all the calculations. ie. the numbers you input no longer populate the entire spreadsheet. In other words, something is getting "lost in translation" and since I am a fairly inexperienced spreadsheet user, I'm not seeing what needs to be adjusted. The particular excel template I've tried to import can be found here:

http://www.vertex42.com/Calculators/deb ... lator.html

It's called the Debt Reduction Calculator, and seems to be a useful tool for getting out of debt fast. Any help moving this template from Excel to OpenOffice would be greatly appreciated!

Hello Yohan,

Well, off the top of my head, and from what you describe, it may be that the Excel file contains some vba code. By default this code does not function within OpenOffice.org - vba routines are not the same as formula within the cells.

I'll take a look at the file and see if I be of any further assistance.

Drew

EDIT - well, I downloaded and tried the file - it doesn't seem to work properly does it...not sure why, but I think I am wrong about the VBA stuff...sorry

Hmm, first of all, there's a hidden sheet. Use Format > Sheet > Show: "Orders" to make it visible. This is the sheet that does all the work.

Now, if you look at the "Orders" sheet, you'll see that it has many errors, which prevent the calculation and make the sheet to appear unresponsive.

The primary problem seems to be the following: there are many calls to the INDEX() function, which is documented like so:

INDEX( ref ; row ; column ; range )

Where only the ref is required. The problem occurs because this sheet has many instances of INDEX() like this:

INDEX( B7:K7 ; $C15 )

I.e., apparently the intention is to pick one value from a single row of values. In this case, the second parameter is meant to select a specific column, but it appears in the row parameter position.

If I change these instances to supply a row parameter of 1, then the whole sheet seems to work.

I suspect this is simply a case where Excel is covering up a mistake rather than throwing an error. The original sheet should probably have the row numbers provided as well, even though it is redundant (there is only one row). Allowing a function to change the interpretation of it's positional parameters is of questionable wisdom.

Ok, I have a version that seems to work. Since the license forbids distributing the sheet, [STRIKE: I won't link to it. If you want it, PM me and I'll email you a copy.] I can't send out copies.

There was one more thing to fix: the drop down entries "Strategy" were not connected to a cell. It may be that Excel will allow a connection using a defined name, where Calc won't. I changed the two drop downs to connect to cell c23, and to use the "position" from the list, noi the list entry.

With all that done, it seems to work, but please don't rely on it! My suggestion is to contact the original web site and see if they are interested in hosting an OpenOffice version. If so, they're welcome to take my changes.

Thanks for the help!

On second thought, I don't think I should offer the modified sheet. Here's what I emailed to yohan:

I know it's probably silly, but I'm very uncomfortable about distributing copies of the modified sheet, given that the very clear terms of the license do not allow it. That, and I don't want responsibility for use of a sheet that isn't tested.

I think all the changes I made are described on the forum if you wanted to do your own.

Probably your best strategy at this point is to find some way to use Excel with that sheet.

Sorry to go back on my offer, I'll change my message on the forum to reflect that.

Yohan (by email) wrote:> I was wondering if you could you break down your fix a little
> clearer. ...

Sure. Here's an example:

Cell Orders.C16 contains this formula:

=INDEX($B7:$K7;C$15)

The documented arguments for the INDEX function are:

INDEX( reference ; row ; column ; range )

So, by that specification, the first argument, reference is $B7:$K7 here, and the second argument row is C$15. The value in $C15 is 2, so this formula should return row 2 from the range $B7:$K7. But that's impossible: that range only has one row, so Calc correctly throws an error (Err504) for that cell.

Excel instead allows the technically invalid formula, and says "there's only one row in the range, so the formula must really be looking for row 1, column 2 from the range $B7:$K7", and it re-interprets the C$15 as the column number, instead of a row number that doesn't make sense.

So Calc is more picky and requires you to write the correct formula, where Excel tries to work around the problem and avoid nagging the user to fix the formula. At least, that's my guess as to what's happening.

To fix the sheet for Calc, you must provide the missing row number argument, which is 1 because the range only has one row:

=INDEX($B7:$K7; 1; C$15)

That works correctly for Calc[*], and it should work also for Excel, and in fact it's probably better for Excel also, because it's bad form to depend on some undocumented behavior.

Most of the formulas are as simple as this example, but some are harder to see where to insert the missing argument. E.g. cell C30: The INDEX function again has only two arguments:
reference: OFFSET($B27;0;B30;1;COUNTA($B$7:$K$7)-B30)
row/column: MATCH(C29;OFFSET($B21;0;B30;1;COUNTA($B$7:$K$7)-B30);0)
So the corrected version looks like this: Using the function wizard (the "f(x)" button to the left of the formula entry) can help you pick out what parts correspond to the different arguments.

BIG DISCLAIMER: I still don't understand all the logic in the sheet, so I could be off track here. It's hard to be sure without having a chance to compare to the same sheet running in Excel. I should have a chance to try it in Excel later tonight.

[*] In fact, Calc will allow an empty second argument in this case, but it seems more safe to go ahead and specify the row as 1.

Ok, I looked at the original sheet in Excel, and what I guessed about the INDEX function does seem to hold up[*][/color].

I also found at least two more incompatibilities, both of which seem really basic to me, such as how empty cells are copied by array expressions. There would need to be more analysis to find formulas that give the same result in both Calc and Excel. Unfortunately, I can't spend more time to sort it all out.

This only underscores (to me at least) that the level of compatibility between Calc and Excel remains far below where it needs to be. Anyone who thinks they can migrate spreadsheets without very careful testing, debugging and re-development, is just asking for disaster.

[*] I also checked in the current OpenFormula draft (OpenDocument - Formula TC). The draft says that INDEX should do what Excel does: if the cell vector has only one row or one column, then the unnecessary parameter can be empty or omitted entirely, and the following parameter is used as the row or column index as appropriate. Calc's INDEX does not do this; I guess it will be changed at some point.[/color]

There was at least one other issue with getting that Excel sheet to work in Calc. I filed an issue because of the subtle incompatibility: Issue 83917: Empty cells referenced through an array are incompatible with Excel.

The workaround is to change the array formulas from e.g.
=TRANSPOSE($Calculator.C8:C17)
to
=TRANSPOSE(IF(ISBLANK($Calculator.C8:C17);0;$Calculator.C8:C17))

One further glitch comes with the COUNT() function. Calc gives the count "#N/A" if any of the range is "#N/A", whereas Excel simply ignores the "#N/A" values.

You can workaround that by changing =COUNT(B15:K15) to =COUNTIF(B15:K15; ">0").

That seems dangerous to me as well (I don't know what Excel does comparing "#N/A" to zero), but I could not figure out any way to count only the numeric values and ignore the #N/As.

That's as far as I got. There are just too many nasty little glitches.