Created attachment 168150 [details]
Simple CALC .ods  file

Probably related to 136864 but I'm not 100% certain what the reporter is actually proposing.

Thank you for reporting the bug.
Unfortunately without clear steps to reproduce it, we cannot track down the origin of the problem.
Please provide a clearer set of step-by-step instructions on how to reproduce the problem.
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the steps are provided

(In reply to Xisco Faulí from comment #3)
> Thank you for reporting the bug.
> Unfortunately without clear steps to reproduce it, we cannot track down the
> origin of the problem.
> Please provide a clearer set of step-by-step instructions on how to
> reproduce the problem.
> I have set the bug's status to 'NEEDINFO'. Please change it back to
> 'UNCONFIRMED' once the steps are provided

There is a fully functioning spreadsheet attached with clear and concise instructions on what cell to read, what is contained in the cell which column to sort on and where to look after the sorting event.
The ascending ordered cell clearly demonstrates the efficacy of the simple formula and the descending order sort clearly demonstrates how the second IF statement does not respond appropriately to the sorting commands.

  Note the cell references in Cell M10
  =IF(J10="";"";IF(J11="";SUM(J7:J10);"")) xx--note the SUM() coordinates
  Note the cell references and result in Cell M434
  =IF(J434="";"";IF(J435="";SUM(J431:J434);"")) xxxx--- SUM() coordinates
  Sort Descending on Column Filter B
  Note the cell references and result in Cell M56 (The last pertinent cell)
  =IF(J56="";"";IF(J55="";SUM(J431:J434);"")) xx-- wrong SUM() coordinates

What could be more specific than the preceding seven lines accompanied by the previously enclosed CALC file?

Please could you provide clear steps to define what I have missed - vagaries don't help anybody

I have reset it to unconfirmed - perhaps somebody could actually read the instructions already provided and then make an informed decision.

What if you ask in ask.libreoffice.org before report a bug?

Using references inside the sort range to the sorted range, it's usually complex, and more complex with referencing ranges.

Maybe easy to test having a sample file with a minimum size of data to reproduce the issue.

Remember here we are volunteers not professionals

(In reply to m.a.riosv from comment #5)
> What if you ask in ask.libreoffice.org before report a bug?

Searched for potential duplicates four times, to no avail. Believed the parameter asking if I wanted to dynamically re-associate sorted cells with their new locations would actually re-associate sorted cells with their new locations. Mea Culpa
> 
> Using references inside the sort range to the sorted range, it's usually
> complex, and more complex with referencing ranges.

Does that mean it works or it doesn't? The entire sheet can be sorted by any one of a number of data columns where there is both direct and nominally "clustered relative" referencing which works fine. I wasn't aware that the limitations were not considered bugs. Mea Culpa 
> 
> Maybe easy to test having a sample file with a minimum size of data to
> reproduce the issue.

If you understand the formula you will realise that all the intervening cells by definition must be empty because they're not the current cell. I attempted to add the function today to a pre-existing file with a simple two-dimensional array and discovered the error / feature / designed redundancy. In order to see the error, you could simply sort descending and look at the first two data lines to ascertain the references had gone about 400 cells out of range. You will note that my first report actually provided in full text the corrupted references so it should have been a simple enough procedure for an investigator to follow. the Final cell simply demonstrated what should have happened when it was inverted. Any way up the result should have been 0.6. I didn't think it was rocket science. Mea Culpa
> 
> Remember here we are volunteers not professionals

I was referring to the developers who whilst they are voluntarily providing software for all of us to appreciate are undoubtedly professional, accomplished programmers. I considered somebody like Mike Kaganski would probably just say "Oh yeah, we are aware of the bug(feature/limitation all you need to do is ------this", Mea Culpa

If it makes you happier - relabel it as NOTABUG. It doesn't work for me so I stripped the function from my spreadsheet anyway. It was simply a forlorn attempt to apprise the foundation of a bewildering feature in the software, in order that they may address the issue in any way they deemed appropriate. Mea Culpa

Sighhhhhhhhhh

Perhaps it would have been pertinent to mention that I attempted to strip out all but three days of data in what I would have assumed would provide a smaller file to examine.
You wouldn't believe the mess that made of all the references.
It even somehow managed to ignore my simple drag to auto-fill attempts with simple =cell above plus one and produced references to non-existent cells that probably used to exist in the non-truncated array but were no longer valid in the current sheet.
For that reason and that reason alone, I considered it was probably more beneficial for the investigator to have the original file and uncorrupted links which could easily demonstrate how/where the invalid references were arising.
I even considered it possible the developers have some diagnostic utilities that permit them to look directly into the memory locations that should have held the data if it hadn't become so corrupt.

Instead of a so much talk, a basic sample file would be the better to identify the bug if there is one.

Usually triage it's done by volunteers like myself, because developers have enough to do, and it sometimes takes a lot of time. 

And it's very important to make the life easy for devs, to know as much as possible what exactly the bug is and how to reproduce it step by step.

As I mentioned, attempting to truncate the range resulted in even more catastrophic erroneous cell references so what I provided is what best demonstrates the feature - WYSIWYG

1. Set autofilter Column C to "All" - this obviates one of the the existing autofilter bugs on newly opened sheets.
2. Ensure autofilter column B is set to "Sort Ascending"
3. Observe the value in M434 - This is the anticipated "Correct Value" 
4. Observe the range references in M434 they are current, 1 following & 3 preceding rows - the preceding rows array grows according to the current location of the focus cell in the daily "cluster"
5. Set Auto Filter on column B - "Sort Descending"

   NOTE: M56 is the new location of the focus cell in the current daily cluster

6. Observe the range references in M56 - they are NOT the current focus array references, they are "out" by 378 rows
7. Observe the value in M56 - this is incorrect

I think steps in comment 0 are pretty clear.
And I repro the problem as described, using "Update references when sorting range of cells" under Options|Calc|General.

With the mentioned setting disabled, I get a different resulting formula in M56: it's

> =IF(J56="";"";IF(J57="";SUM(J53:J56);""))

and it is much saner.

I suppose that you should check if this setting solver this for you, or use things like OFFSET in your formula. My opinion is that it works as designed, taking into account the absolute impossibility to define "updating references to ranges (which may change randomly) when sorting".

(In reply to Mike Kaganski from comment #10)
> I think steps in comment 0 are pretty clear.
> And I repro the problem as described, using "Update references when sorting
> range of cells" under Options|Calc|General.
> 
> With the mentioned setting disabled, I get a different resulting formula in
> M56: it's
> 
> > =IF(J56="";"";IF(J57="";SUM(J53:J56);""))
> 
> and it is much saner.

I get the same result as you but on closer examination, you will discover that the cells being SUMmed are now future events for which there can never be any valid data. It should only produce a result for the current event and any pertinent "earlier" event cells. I have already established that reversing a SUM() reference viz SUM(a1:a10) gives the same result as SUM(a10:a1) but the reverse sort doesn't simply reverse the range reference - it moves the range from say current-3 to current +3 that is to say, it is no longer (J53:J56) it should become (J56:J59) - the cells move from a lower to a higher order because the array relationship is now reversed. The cells being considered are exclusively "today's" events so their offset is coded manually into the formula for each event in the daily cluster, otherwise there would be a requirement for further "is it the first or last pertinent event of the day" verification. 
> 
> I suppose that you should check if this setting solver this for you,

No such luck, it still malfunctions. The setting works as anticipated for all other scenarios and if I make the global change I fear it could easily mess up many other documents where there is no second conditional range in the formula

 or use
> things like OFFSET in your formula. My opinion is that it works as designed,
> taking into account the absolute impossibility to define "updating
> references to ranges (which may change randomly) when sorting".

I also wondered about OFFSET but the formula would somehow have to take the sort order into consideration. How does one detect the current sort status - Ascending or Descending - and then adjust the OFFSET appropriately?

If you're happy the software performs as designed and that my attempt is beyond the capabilities of a spreadsheet then it's probably best to reject the report as  NOTABUG. I was just trying to put some cream on the candy - it's proving to be more of a cyanide pill :).

I'm not arguing with the precept of cells potentially ending up anywhere with sorting but the structure of my array actually forces the events into a logical sequence for the task which precludes the possibility of randomised sorted locations AND I just don't sort on some of the columns, they're there because of their enforcement by the auto filter regime.

(In reply to Colin from comment #11)
> (In reply to Mike Kaganski from comment #10)
> I get the same result as you but on closer examination, you will discover
> that the cells being SUMmed are now future events for which there can never
> be any valid data.

Of course. And this shows how you use Calc wrong. You use relative references *as a substitution for a semantical relation". This is implying that Calc "understands" your ideas about your data, such as "I refer to these N preceding events", instead of "N cells above". It's you who has the mental model that cells above are preceding events, but don't imply Calc to be human.

If you want to refer to data that has some semantical relation to this cell, independent of relative position on sheet, then you should create formulas based on those traits, not on relative referencing.

How to create such a formula for a specific task is off-topic here, please ask e.g. on ask.libreoffice.org.

Closing NOTABUG.