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.
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.
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.
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.
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 
Appreciate that fact however I was hoping there was same way around having to modify many s/s. But thanks for the answer
“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