asked 2012-02-21 15:32:47 +0200

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

Anonymous

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

3 Answers

Sort by » oldest newest most voted

answered 2012-02-22 00:00:51 +0200

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

updated 2012-02-22 00:00:51 +0200

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.

link delete flag offensive edit

Comments

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.

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

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.

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

answered 2012-02-21 17:05:36 +0200

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

updated 2012-02-21 17:05:36 +0200

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

link delete flag offensive edit

Comments

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.

Jeremy Andrews ( 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 :)

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

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

answered 2012-09-27 14:09:46 +0200

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

updated 2012-09-27 14:09:46 +0200

mideal
11 ● 1

"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

link delete flag offensive edit

Resources

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!

Question tools

Stats

Asked: 2012-02-21 15:32:47 +0200

Seen: 244 times

Last updated: Sep 27 '12

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

3 Answers

Comments

Comments

Resources

Donate

Question tools

Stats

Related questions