Calc Cell Formula Works Differently In Two Different Documents [Resolved]

[mendy]

Hi visited topic at
https://ask.libreoffice.org/t/contents-did-not-showing-in-libreoffice-calc-data-validity-under-input-help/95819/7

Was inspired to use method to spiffy up one of my forms. Reworked my form and now in four fields a 0 shows up when previously it was blank till I entered data. At first thought it was something (still could be) wrong with my formulas in those fields. Copied the formulas to a new sheet and both to same row and correcting cell references if in different row. Attached is a cut down sample to illustrate my question. Shows correctly but if copied to new spreadsheet it shows the zeros. On working form if I add sheet they work there. Thanks for looking.

Working Formula Short Version.ods (217.6 KB)

Instructions to show issue are in file.

[Zizi64]

@mendy

Shows correctly but if copied to new spreadsheet it shows the zeros.

.
Please upload such sample too.
.
Your sample and the copy/pasted cell range B8:J18 works for me in a new file too.

[mendy]

Thank you for looking, here is the sample I get as result of the copy/paste. I am attaching both copying the cell range and copying the row range. Both show the zeros.

@Zizi64

Your sample and the copy/pasted cell range B8:J18 works for me in a new file too.

Do Working Formula Short Version.ods and your copy/pasted new file open with no zeros with no data entered? Image from opening on my system.
Edit: Image at right in first sample shows the zeros with no data
Possibly may have to start from scratch or wait for update. Edit changed image to show both openings. Disregard grids view.

Copy Rows.ods (25.9 KB)

Copy Cell Range.ods (24.4 KB)

[mendy]

Resolved
Not sure why but it worked on existing file but not on modified file in my system. Exploring tried changing the format from 0 to 0;; the zeros went away. Saving / closing / reopening now shows format [>0]0;[<0]"";"" . Must convert on save and reopen. It works happy now.
Thanks again @Zizi64 for helping and got me thinking and searching.

[erAck]

Fwiw, the format codes 0;; and [>0]0;[<0]"";"" are semantically identical. For 0;; with three sections/subformats the first subformat 0 has the implicit condition “for values >0”, the second (empty i.e. no value displayed) subformat has the implicit condition “for values <0”, and the third (empty i.e. no value displayed) subformat has the implicit condition “for other values” (i.e. here =0).

See Number Format Codes.

The format code 0;; when saved in ODF .ods is not stored as the format code but as a description of the properties of the format that when loaded results in the conditions and the empty display strings being explicitly stated in the format code.