openoffice calc formula fails under ubuntu

make attention to the decimal separator is it "." or "," ? this can be depending on locals installed in your ubuntu system

Without seeing the spreadsheet in question I can't say for certain. However, #VALUE! usually means that something referenced in the formula doesn't make sense, such as attempting to add '3' and 'asdf'. (Help→OpenOffice.org Help→Index tab, type 'error' and look for "error codes, list of")

Are you sure that E2, E8, F2, and F8 all contain numbers? Make sure that they're numbers and not text versions of numbers. (ex. A1=3 and A2='3 both display the same, yet A1 is a number and A2 is text.)

As an aside, the SUM function is superfluous for such basic operations. This would work just as well:

=E2+E8-F2-F8

Were you able to find the issue, argetm?

I have exactly the same problem with an ods imported from win xp. The formula fails with #Value when it reads an empty cell. It should read an empty cell as zero. If I manually enter a zero into the empty cell the formula then works. It only applies to imported sheets. An formula entered directly into the Ubuntu sheet works ok. It is a showstopper.

You will need to provide an example. I created a spreadsheet as described by argetm in XP, rebooted into Ubuntu and opened the spreadsheet with no problem.

Tony

As I understand it, opening is not the issue. It's representation of empty cells as 0 (zero, the number), as opposed to 0 (zero, the numeral), or '' (the empty string).

Try creating a simple spreadsheet in Excel that has
A1: '15' (Fifteen, the number)
A2: '' (Empty, nothing in it)
A3: '15' (Fifteen, the number)
B1: '=A1+A2+A3'

The result should be '30' (15 + 0 + 15). What he's experiencing is that when saving, then opening in OpenOffice, it's resulting in '#Value!'. Our supposition is that it's because OpenOffice is interpreting A2 as a string, not as a number.

I did delete one of the cell contents and got no error. So 'empty' is treated as zero. Now, if I 'delete' the contents by hitting the space key, of course I get an error because the cell has a space character in it. If that is what argetm wants (ie to get spaces treated as zero) he will need to test the type of each cell and replace the cell value with zero if it is not numeric.

Tony

No.

Deleting is not the issue. Once you mess with it in OpenOffice, it will get it right. That's not the issue.

Trying again: the issue is in /translation/ from Excel to OpenOffice. He is saving in /Excel/, then opening in OpenOffice. Before he does anything, the sheet has the #Value! symbol. Of course we can fix it, but at the point he has to mess with it, it's lost it's value. It *has* to be 100% interoperable, and an issue like this is not.

While I think your solution will work, that's a heck of a lot of work when it shouldn't even be an issue. I would consider this a bug in one spreadsheet program or the other. Without delving further into it, I can't say which.

Kevin

There is no mention of Excel in either agetm or C M Todd's question. argetm says he is using open office calc and C M Todd says its an ods file.

Tony

Mea culpa, you are right. I'm sorry, definitely got three different tabs confused. Sorry for the noise. Heh, on *all* questions.