is there anyway to get the maths functions to treat a non-numeric as 0? Like open office does.

This post is a wiki. Anyone with karma >750 is welcome to improve it.

is there anyway to get the maths functions to treat a non-numeric (Space or dash) as 0 (Zero)? Like open office and Neo do?. The latest version looks great but I have this problem and it really makes a mess of very complex spread sheets.

delete close retag edit

Sort by » oldest newest most voted

This post is a wiki. Anyone with karma >750 is welcome to improve it.

cloph
2802 4 18 43

The "N" function does return 0 for all text and FALSE. The change was done to match the specification regarding simple arithmetic operators, so expect OOo and NeoOffice to follow..

The real question is: Why are you returning a non-numerical value when you want to calculate with it? Just use a proper cell-format to hide the 0. Be it with conditional formatting, the style function or whatever other method.

I wanted the cell to return a "-" if a condition is not met so its easy to read. Could change the formula but I have so many sheets with the formula in that maintenance is a problem. Will use Neo or Ooo and do the maintenance, or Libre has a solution.

( 2012-02-25 10:56:03 +0200 )edit
1

well, using "-" instead of using a style/format (STYLE function) was a mistake. "Current" versions (i.e. old codebase) of Neo and OOo will show the old behaviour, I wouldn't count on them preserving this, but rather also adapt to the standard's definition. Search & replace might solve your problem.

( 2012-03-08 15:59:54 +0200 )edit

This post is a wiki. Anyone with karma >750 is welcome to improve it.

Pedro
3218 4 23 54

Can you provide an example of a Math Function that has this problem? A SUM of 2,-,1 (in consecutive cells) does return 3, and the AVERAGE does return 1.5 so the cell with - is treated as Zero...

I have in a cell =IF(F20>0,F20D$6/(100+D$6),"-") or =IF(F20>0,F20D$6/(100+D$6)," "). I then have =F20-G20 which returns "VALUE". Ooo gives 0. I could change the formula to = IF(F20>0,F20*D$6/(100+D$6),0) but I many, many s/s used by a lot of excel users via dropbox so maintenance is a problem.

( 2012-02-25 10:50:54 +0200 )edit

Actually #VALUE! is correct and 0 (zero) is not. Your formula (=F20-G20) is trying to do a calculation with a text cell (both " " and "-" are text, that is why you have to enclose it with double quotes). Excel also reports #VALUE! This is not a bug. It is the correction of an existing bug :)

( 2012-02-25 11:39:34 +0200 )edit

Appreciate that fact however I was hoping there was same way around having to modify many s/s. But thanks for the answer

( 2012-02-27 11:09:24 +0200 )edit

This post is a wiki. Anyone with karma >750 is welcome to improve it.

"A SUM of 2,-,1 (in consecutive cells) does return 3, and the AVERAGE does return 1.5 so the cell with - is treated as Zero..." Please recalculate: (2+0+1)/3 = 1, so the "-" is treated as "missing value" (2+1)/2 = 1.5

Donate

LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!