Treatment of mathematical operations on text

[petef1]

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.

[mariosv]

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!

[petef1]

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

[mariosv]

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.