Ask Your Question

treatment of mathematical operations on text [closed]

asked 2013-02-24 11:59:04 +0200

petef. gravatar image

updated 2020-08-02 17:22:01 +0200

Alex Kemp gravatar image build 5b93205

I have just installed LibreOffice and am trying to migrate my Staroffice .xls spreadsheets. The .xls spreadsheets are openable with LibreOffice, but there is a problem with the treatment of non-numerical data when mathematical operations are performed. LibreOffice returns the error #VALUE! when it finds non-numerical data in a cell that is being operated on mathematically (this is correct according to the HELP Manual). StarOffice treats this situation differently, it just ignores the non-numerical data, so the mathematical operation can complete sucessfully.

EXAMPLE: In the formula =SUM(OFFSET(H683,-1,0),C683,-D683,E683) If one cell referenced by the formula has something other than a numerical value in it, then

  • LibreOffice returns #VALUE!
  • StarOffice ignores the non-numerical cell, and returns the
    outcome of the calculation. This is
    very useful in large complex
    spreadsheets. Is there a way round
    this in LibreOffice? I have tried to find one without success.


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-23 14:20:36.392537

1 Answer

Sort by » oldest newest most voted

answered 2013-02-24 17:21:08 +0200

m.a.riosv gravatar image

I think N() function can solve the issue (LibreOffice help):
Returns the numeric value of the given parameter.
Returns 0 if parameter is text or FALSE.
If an error occurs the function returns the error value.
Value is the parameter to be converted into a number.
N() returns the numeric value if it can.
It returns the logical values TRUE and FALSE as 1 and 0 respectively.
It returns text as 0.
=N(123) returns 123
=N(TRUE) returns 1
=N(FALSE) returns 0
=N("abc") returns 0
=N(1/0) returns #DIV/0!

edit flag offensive delete link more


Thanks. This will work as a workaround. I think in the long run I'll need to redesign my spreadsheets!

petef. gravatar imagepetef. ( 2013-02-25 09:52:06 +0200 )edit

The last versions are more strict using text as numbers. I think only text numbers/dates with a no dubious interpretation, and an only one cell reference can be used.

m.a.riosv gravatar imagem.a.riosv ( 2013-02-25 11:32:31 +0200 )edit

Question Tools


Asked: 2013-02-24 11:59:04 +0200

Seen: 244 times

Last updated: Feb 24 '13