openoffice calc formula fails under ubuntu

Asked by argetm

I use open office calc to track household expenses. The formula =sum(E2+E8-F2-F8) displays #value! instead of the result of the computation. I have used the same formula in Open Office Portable Apps Calc and Open Office Calc in windows xp all versions 3.01 and it works. What is the problem with using this same spreadsheet in Open Office Calc within Ubuntu? The spreadsheet is on a thumb-drive and is supposed to work in all these systems.

Question information

English Edit question
Ubuntu Edit question
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Nizar Kerkeni (nizarus) said :

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

Revision history for this message
Kevin Hunter (hunteke) said :

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→ 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:


Revision history for this message
Kevin Hunter (hunteke) said :

Were you able to find the issue, argetm?

Revision history for this message
C M Todd (christopher-todd879) said :

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.

Revision history for this message
Tony Pursell (ajpursell) said :

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.


Revision history for this message
Kevin Hunter (hunteke) said :

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.

Revision history for this message
Tony Pursell (ajpursell) said :

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.


Revision history for this message
Kevin Hunter (hunteke) said :


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.

Revision history for this message
Tony Pursell (ajpursell) said :


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.


Revision history for this message
Kevin Hunter (hunteke) said :

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

Can you help with this problem?

Provide an answer of your own, or ask argetm for more information if necessary.

To post a message you must log in.