by **Phillip** » Mon Jul 08, 2019 10:57 am

I completely accept that Zizi64 is right. At the same time a cell is interpreted as a numeric, value AND a string, which can either be the default, entered values, or the result of a formula.

I also accept this is how spreadsheets work.

The reason I raised this post is to report the inconsistency in processing and displaying.

(To save viewers looking at what has been said before in this post):

1: An unused cell and behaves as: An empty string, numeric zero for calculations gives TRUE for ISBLANK(), and IS ignored by numeric functions like COUNT(), AVERAGE() etc.

2: '=(unused cell) behaves as: An empty string, numeric zero for calculations gives FALSE for ISBLANK(), and is NOT ignored by numeric functions like COUNT(), AVERAGE() etc.

3: '="" behaves as: An empty string, numeric zero for calculations gives FALSE for ISBLANK(), and IS ignored by numeric functions like COUNT(), AVERAGE() etc.

4: '=0 (A real zero) behaves as: An empty string, numeric zero for calculations gives FALSE for ISBLANK(), and is NOT ignored by numeric functions like COUNT(), AVERAGE() etc.

(1) and (3) display nothing, (2) and (4) display a zero (Unless you switch this off via Tools>Options>Open Office Calc> View, in which case BOTH display nothing, so no help at all!!.

Using ISBLANK() to distinguish between (1) - TRUE and the rest - FALSE - no problem.

It still seems to me that since CALC can work out the difference for '=""(4), why not for '=unused cell(2)

or in other words

- if a formula results in a numeric value ( 1+2-3) then it is a real zero. (As now)

- if a formula results in a non-numeric value then it is NOT a real zero (and so is ignored by COUNT(), AVAERAGE() etc yet is still processed as zero or a string as before)

Either setting a cell to a string '="" (CALC knows it is NOT a real zero - as now)

Or a copy of an unused cell - "No numeric value has ever been set" (CALC decides IS a real zero) ?????.

Finally - I accept that this is how all spreadsheets work, and we will have to use formulae such as '=IF(OR(ISBLANK(A1);NOT(ISNUMBER(A1);"";A1)...

And also thank Zizi64 for all his help on this and many other posts.

Oh if you do hear of a spreadsheet program that can see the difference between real zeros and ... do let me know!

Open Office 4.1.5, Windows 7 Professional 64-bit