Description: There appear to be two impacts from this situation. The first scenario was only apparent when the sheet was initially created and the original formula in F5 was; =E5/SUM('C') which was replicated for the entire data column and produced validated results. I considered it unusual that the result in F2 was precisely ONE. I changed A1 and whilst all the other columns reflected that change, Column F and the result in F2 remained unchanged. I then filtered three days from Column C and whilst the rows were hidden the result in F2 was still ONE. I then changed the formula in F5 to include the ad hoc label ‘B’ instead of ‘C’ and replicated that for the entire column F2 and the entire Column F changed to reflect the amended formula and amendments to A1 affected all appropriate cells. Subsequently, I attempted to change the formula in F5 back to the original ad hoc label ‘C’ only to discover that the system now considers label ‘C’ to be ZERO and produces the requisite #DIV/0! Error. Also, a new cell E1 with the simple formula =SUM(‘C’) returns ZERO – What happened to the ad hoc label? Steps to Reproduce: To demonstrate that it originally provided a result it will be necessary to create a new version of what I have attached and ensure that the formula in F5 is initially created to refer to label C =E5/SUM(‘C’) and then replicate the formula - LO has been observed to automatically insert the apostrophes Observe that F2 = 1 Edit A1 to 9 Observe F2 remains at 1 and column F is unchanged Filter out three days from column C – label ‘A’ Observe F2 remains 1 Edit F5 to read =SUM(‘B’) Observe the changes Attempt to amend F5 to the original =SUM(‘C’) Observe the destruction Actual Results: Case 1 appears to evaluate the array but subsequently doesn't reflect any changes to values. Case 2 destroys the ad hoc label and returns #DIV/0! error Expected Results: Regular assessment of the values in the source cells and ad hoc label array Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Version: 7.2.1.2 (x64) / LibreOffice Community Build ID: 87b77fad49947c1441b67c559c339af8f3517e22 CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 175892 [details] Simple example sheet Remember: The instructions are to first create the sheet yourself using this as an example as one error is "first run only" in that it can never be replicated once the formula has been changed
(In reply to Colin from comment #0) > Edit F5 to read =SUM(‘B’) > Observe the changes > Attempt to amend F5 to the original =SUM(‘C’) > Observe the destruction > After editing F5 to SUM('B')then make a few changes to A1 and filter the days to demonstrate that column F and F2 are now responding appropriately
(In reply to Colin from comment #0) > Description: > There appear to be two impacts from this situation. > > The first scenario was only apparent when the sheet was initially created > and the original formula in F5 was; > > =E5/SUM('C') > > which was replicated for the entire data column and produced validated > results. > > I considered it unusual that the result in F2 was precisely ONE. > > I changed A1 and whilst all the other columns reflected that change, Column > F and the result in F2 remained unchanged. > > I then filtered three days from Column C and whilst the rows were hidden the > result in F2 was still ONE. > > I then changed the formula in F5 to include the ad hoc label ‘B’ instead of > ‘C’ and replicated that for the entire column > > F2 and the entire Column F changed to reflect the amended formula and > amendments to A1 affected all appropriate cells. > > Subsequently, I attempted to change the formula in F5 back to the original > ad hoc label ‘C’ only to discover that the system now considers label ‘C’ to > be ZERO and produces the requisite #DIV/0! Error. > > Also, a new cell E1 with the simple formula =SUM(‘C’) returns ZERO – What > happened to the ad hoc label? > > Steps to Reproduce: > To demonstrate that it originally provided a result it will be necessary to > create a new version of what I have attached and ensure that the formula in > F5 is initially created to refer to label C > =E5/SUM(‘C’) and then replicate the formula - LO has been observed to > automatically insert the apostrophes If I fill the formula, I get these: =E6/SUM(#NAME?$E5)
(In reply to Buovjaga from comment #3) > (In reply to Colin from comment #0) > > If I fill the formula, I get these: > > =E6/SUM(#NAME?$E5) I'm Not sure whether you're telling me that there's a third manifestation (are you validating the same Win10 release) or it's behaving as planned and rejecting some error I have created myself.
(In reply to Colin from comment #4) > (In reply to Buovjaga from comment #3) > > (In reply to Colin from comment #0) > > > > > If I fill the formula, I get these: > > > > =E6/SUM(#NAME?$E5) > > I'm Not sure whether you're telling me that there's a third manifestation > (are you validating the same Win10 release) or it's behaving as planned and > rejecting some error I have created myself. I was just informing you and others that I tried to reproduce the problem, but was stumbling immediately in the first step. For you it does not behave like that? I tested on Linux.
(In reply to Buovjaga from comment #5) > (In reply to Colin from comment #4) > > (In reply to Buovjaga from comment #3) > > > (In reply to Colin from comment #0) > > > > I was just informing you and others that I tried to reproduce the problem, > but was stumbling immediately in the first step. For you it does not behave > like that? I tested on Linux. I was at least able to complete the procedure once but the moment I edited the formula to modify the label it failed. Probably just a difference exacerbated by the operating system variants. I'm on this Version: 7.3.6.2 (x64) / LibreOffice Community Build ID: c28ca90fd6e1a19e189fc16c05f8f8924961e12e CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded now so perhaps I should revisit and log any differences