# treatment of mathematical operations on text [closed]

3.6.5.2 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.

Thanks.

edit retag 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

Sort by » oldest newest most voted

I think N() function can solve the issue (LibreOffice help):
N
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.
Syntax
N(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.
Example
=N(123) returns 123
=N(TRUE) returns 1
=N(FALSE) returns 0
=N("abc") returns 0
=N(1/0) returns #DIV/0!

more

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

( 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.

( 2013-02-25 11:32:31 +0200 )edit