Created attachment 105707 [details] A calc sheet with the anomalies of the "frequency" function shown and described Problem description: The frequency matrix function has changed its behaviour and it is not sensibly usable anymore. The old function could be inserted into an arbitrary number of cells and it ended up occupying just in the cells you selected before writing it. The most common situation was that: - you had a data column of arbitrary size, - defined a column vector of upper limit for intervals of value (or specific single values for discrete data) for which you wanted to obtain the frequency counts in the data column (occupying, for example, k cells in all). - Then you selected the column range of k cells just to the right of the cells containing upper limits, typed the “frequency” function with the two inputs (data column and upper limits column) and inserted it with CTRL+SHIFT+enter - Afterwards, in old versions of LO the matrix function could be edited, moved around or copied and pasted simply re-selecting the k cells where you did input it. - Now in 4.3.1 the function can be inserted pre-selecting any number of cells (even only one), but the problem is that, after entering it, it occupies a number of cells which is not clear, but is bigger than the column of k cells where you see frequency counts!! In other words there are some cells occupied by the function but that when made the active cell clicking on them with the mouse do not show the formula in the formula bar, which is absurd. In fact, if you select the range where you inserted the formula (the k cell column right of the upper limits), you cannot modify the formula now (it says that the selection is only “a part” of the matrix), you cannot copy the cell range, nor you can drag it around. It is impossible to edit the function inserted, as by trial and error I discovered that you need to select both the column before (left) and after (right) the one where you put the formula, and in doing so you will not have the formula text in the formula editing bar as was the case before. It is clear that something is wrong also because if you save your calc file and open it with Openoffice or LO old releases (I tried the first), then you'll see another frequency value in the lower k+1 cell which looks like it is part of the matrix formula... but it cannot be seen on LO!! If you re-save the file without any user induced change there and re-open it with 4.3.1, everything is changed: now the "frequency" formula occupies the k desired cell plus the k+1 below them, and it's only there, differently from how it was before! This shows an inherently unstable behaviour, and can easily lead to data corruption, impossibility to modify and edit our files. The problem is critical for people like me who rely on the "frequency" function very often: it is a showstopper in itself, enough to go back to older releases Expected behavior: the one that the "frequency" function exhibited in older releases, similar to all other matrix functions Operating System: Windows 7 Version: 4.3.1.2 release
the problems is there in 4.3.0.4 as well. I have marked as critical because the "frequency" function is capitally important if you do any stats with calc, and because its behaviour is VERY VERY FAR from what it should be. Thanks for the attention
I have now reverted to 4.2.6.3 (mainly to avoid problems with formula editor as in bug 80650). Unfortunately, the "frequency" function still has the erratic behaviour as in 4.3.1.
After some further fiddling, I can state the situation more precisely: - in LO 4.2 or 4.3 when you input a "frequency" function in a formula, the visible result is correct but the cells occupied by the formula are not clearly defined, it depends on what you need to do: - if you move the active cell around and each time look at what is shown in the formula bar, you see the "frequency" formula only in the correct cells where frequency values are shown. E.g. if you inserted a 5 interval frequency count into d3:d7, only when you have one of these cell active you see the formula in the formula bar; - if you need to copy or move by mouse dragging the counts and the formula behind them, you cannot do that with d3:d7, which is what usually (and correctly) happens when a matrix formula covering many cells is only partially selected. The problem is that here the selection is correct (and in fact in previous releases it worked). After some trials you can see that the minimum range of cells you have to select to be able to copy/drag is actually C3:E8 in our example, or in general you must add two columns one on each side of the one REALLY containing the formula, AND a row below. In reality, other contents could be in the additional cells, possibly having nothing to do with the "frequency" formula, and thus you are forced to copy/drag them as well. - if you want to edit and modify the formula, I still have not found a cell range allowing me to do that: any range will tell you that "you cannot modify a part of a matrix formula". So at the moment I fint it IMPOSSIBLE. Normally you would just select the range D3:D7, click on the formula bar and edit it. This all happens in a single session, in which you have created the formula with "frequency" function and interact with it without saving closing and reopening the file. In fact, if you proceed to do that, everything changes!! When you reopen the file the situation is strongly different. This is it itself a significant bug: there is inconsistency between what you think you are saving and what is really saved. - To be precise, after reopening the calc file the frequency formula appears to have extended itself one cell further down, i.e. to D3:D8 now, instead of D3:D7, and in D8 there is in fact an additional value (which is the count of possible values in the data column higher that the high upper limit you gave as second argument to the function - normally it is zero as you would like to include all values in your frequency count). - Now the consistency between the cells where values are shown and those actually occupied by the formula is OK, and you can edit the formula in the usual way; however the unwanted final value in D8 cannot be eliminated because is part of a matrix function! and of course if you recreate the formula without it, then erratic behaviour described starts again. I am really surprised that nobody has run into this problem else than me. Please note that all of this is being experienced on the Italian locale with the "FREQUENZA" function actually; if this is not the case in other locales please let me know. Thanks for your attention
May be this bug can be solve by reading the help for FREQUENCY function... FREQUENCY returns an array whith one more row than the "limit range" (2 arg of the function). You don't need to select a range before entering the formula. Select only the 1st cell, enter the formula. Then Ctrl+Shift+Enter adapt the range to the 2nd arg of the function. If you want to modify an array formula, put the cursor in the 1st cell containing the formula and press Ctrl+/ (the entire range is selected).
Hello and thanks for your suggestions. What you write is true, but it is different from the behaviour of the function in previous versions (and from that of present OO release 4.1.1). >>>>> BEFORE: - if you inserted the function preselecting one cell (as you say) or k+1 (with a k array of limit values) you got the k+1 counts, k BELOW you limit values and the last ABOVE the last limit value. - if you inserted the function preselecting ANY other number of cells (2, 3, ... k) you got results only in the corresponding cells, the function was present only in those cells, and could be moved, copied and edited just selecting the same cells, no more no less. This happened in particular if you pre-selected k cell, non wanting the count above your last limit value because you designed the limit values so as to include all case. >>>>>> NOW: - in the first case nothing has changed; - in the second case above you get the partial results in the pre-selected cells, but the inconsistency between what results you see and what cells are part of the matrix that I described in previous comments is there. I think that in this situation, with no apparent error of which the user is alerted, the behaviour is - and looks - very amazing and irrational. When you save close and reopen, results are forced to be shown in full, regardless of your pre-selected range, so what has happened is that it is actually NO MORE POSSIBLE to obtain and show partial results of a matrix function. Prompted by your comment I extended the analysis to other matrix functions, noticing that is holds for many (probably all) of them: so there is SOMETHING THAT HAS BEEN CHANGED IN THE BEHAVIOUR OF MATRIX FUNCTIONS IN GENERAL. I was not aware of this change and the help documentation does not make it clear. IMHO the previous situation was much preferable: - now you have lost a functionality (the ability to produce only partial results from a matrix function), that you may consider not so relevant, but maybe relevant to some; - the behaviour of the sheet when you pre-selct a partial range of cells to insert a matrix function appears to be strange and inconsistent, since the ever-present link between what you insert in cells and what you see in the formula bar is lost, even if you get no error message. And in fact, when you save and reopen things are different. It would have been better, then, that in this case you got the "forced full results" immediately, not only after closing and reopening the file. Any further comments of hints are welcome.
I can reproduce and confirm, removing NEEDINFO.
I can confirm that the bug is still in the actual release version 4.3.2.2
@Omar: Please don't touch the Version field, it indicates the earliest version a problem occurred, thank you. See also https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Version
I'm investigating this.
Actually this isn't related to the FREQUENCY() function, but any matrix/array function entered into a preselected smaller cell area than the resulting matrix dimensions. Additionally, the larger internal dimension is saved as cell area to the document file and thus extends the occupied cell area when loading the document.
*** Bug 89133 has been marked as a duplicate of this bug. ***
Yes, that is true, I wrote about the Frequency function only because I happened to use that function when discovering the problem. It is actually as you say, and I must add that it makes dealing with matrix functions a bit of a nightmare: when I use them with students during lab sessions I am always worried to end up with an unwanted invisible occupation of cells by a matrix function, a thing that I would have difficulty in explaining even as a bug. Any effort to solve this problem would be greatly appreciated!
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e32eff2bb4c12fdc33e476b9f12bb4bb71d22ebc Resolves: tdf#83461 do not override MatColsRows if already set It will be available in 4.5.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/14354 for 4-4 https://gerrit.libreoffice.org/14355 for 4-3
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5236e24afddeba986802dea10f988f0559365ff8&h=libreoffice-4-4 Resolves: tdf#83461 do not override MatColsRows if already set It will be available in 4.4.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cd1f5a81b7602040a7c88b30fd8dbde844fa2f71&h=libreoffice-4-3 Resolves: tdf#83461 do not override MatColsRows if already set It will be available in 4.3.7. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.