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 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 '=""(3), 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 (As now) - or a copy of an unused cell (Not as now)

then it is NOT a real zero (and so is ignored by COUNT(), AVERAGE() etc yet is still processed as zero or a string as before)

As it is: Setting a cell to a string '="" (CALC knows it is NOT a real zero - as now)

Setting a cell to an unused cell - "No numeric value has ever been set" (CALC decides it 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!

(Minor edits made on 10 Jan 21 to make more clear)

Last edited by

Phillip on Sun Jan 10, 2021 10:08 pm, edited 1 time in total.

Open Office 4.1.8, Windows 7 Professional 64-bit