Treatment of mathematical operations on text 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.


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!

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

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.