Description: TRANSPOSE() function produces an result which is incorrect and should warn when operation can not be performed due to being out of bounds. Steps to Reproduce: 1. In cells XFC1:XFD4, enter 1, 2, 3, 4, 5, 6, 7, 8 2. In cell XFC6, enter the formula =transpose(XFC1:XFD4) 3. Press Enter 4. Cell XFC6 shows the value 1, and there are no other elements in the transpose matrix as the matrix would be outside the bounds of the sheet Should there be a warning or an error message when the operation can not be done due to the values extending beyond the boundary of the Sheet? Actual Results: 1. Produces an incorrect value. The result is a value in only 1 cell which is not a transpose of the matrix. 2. No feedback provided when operation can not be performed. Expected Results: 1. Should show an error value in cell instead of 1. 2. Feedback message when operating can not be performed due to out of bounds. Reproducible: Always User Profile Reset: No Additional Info: Thank you
Add #SPILL! error 1. Spill range isn't blank 2. Indeterminate size 3. Extends beyond the worksheet's edge 4. Table formula 5. Out of memory 6. Spill into merged cells 7. Unrecognized/Fallback How to correct a #SPILL! error https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023
Can you please change the Status to NEW?
(In reply to Óvári from comment #2) > Can you please change the Status to NEW? Unfortunately, comment 0 has nothing to do with comment 1, and the original Summery field has nothing to do with the change you made on it. So, ATM, I would suggest setting this report as invalid, unless you are willing to get this report back in track (and know how to do it). BTW, the #SPILL! error you mentioned in comment 1 would make no sense at this time in Calc, as the feature that would potentially trigger such error is not supported in Calc ATM.
However, TRANSPOSE() silently omitting values if the transposed matrix does not have sufficient cells dimension to be held should generate _some_ error, IMHO.. probably #REF! but if Excel uses #SPILL! (does it?) for this very situation then we could introduce that as well. So maybe we should change this request from anything #SPILL! error related to the actual use case described. Doing so.
(In reply to Eike Rathke from comment #4) > However, TRANSPOSE() silently omitting values if the transposed matrix does > not have sufficient cells dimension to be held should generate _some_ error, > IMHO.. probably #REF! but if Excel uses #SPILL! (does it?) for this very > situation then we could introduce that as well. > > So maybe we should change this request from anything #SPILL! error related > to the actual use case described. Doing so. Now we are back to the original Summary field. FWIW, TRANSPOSE() is not a new function (as Eike knows), whereas the #SPILL! error is directly related to the post-array-formulas era (i.e. dynamic arrays), not supported by Calc ATM. While I see the potential need to receive some kind of warning regarding the procedure described in comment 0, I am not sure that getting an error on the cell result (e.g. #REF!) is adequate; the result within the cell is correct. There are similar operations that will get a warning, not an error in valid cells. For instance, copy an entire column with content on row 17000 and then "paste special > transpose" to some row will trigger the warning: "There is not enough space on the sheet to insert here." The negative consequence is that _no_ cell gets a result, but the user is warned. I'm not sure the balance between the pros and cons is positive, in any of these cases.
(In reply to ady from comment #5) > While I see the potential need to receive some kind of warning regarding the > procedure described in comment 0, I am not sure that getting an error on the > cell result (e.g. #REF!) is adequate; the result within the cell is correct. But that is only _one_ scalar value cell (no matter how many rows or columns are actually missing), not even a hidden matrix behind it. Again, what does Excel do?
Created attachment 191374 [details] TRANSPOSE #SPILL! (In reply to Eike Rathke from comment #6) > (In reply to ady from comment #5) > > While I see the potential need to receive some kind of warning regarding the > > procedure described in comment 0, I am not sure that getting an error on the > > cell result (e.g. #REF!) is adequate; the result within the cell is correct. > But that is only _one_ scalar value cell (no matter how many rows or columns > are actually missing), not even a hidden matrix behind it. > > Again, what does Excel do? #SPILL!
Created attachment 191375 [details] TRANSPOSE Spill range is too big (In reply to Eike Rathke from comment #6) > (In reply to ady from comment #5) > > While I see the potential need to receive some kind of warning regarding the > > procedure described in comment 0, I am not sure that getting an error on the > > cell result (e.g. #REF!) is adequate; the result within the cell is correct. > But that is only _one_ scalar value cell (no matter how many rows or columns > are actually missing), not even a hidden matrix behind it. > > Again, what does Excel do? #SPILL! Spill range it too big
(In reply to Óvári from comment #8) > #SPILL! > > Spill range it too big The question is (or should be) what happens (in Excel) when you use the TRANSPOSE() function as a normal array function (introduced with CSE), as oppose to using it as a dynamic array function. Calc does not support dynamic arrays features. Would you please test the case with TRANSPOSE() function as normal array? What error (if any) Excel shows in such case?
(In reply to ady from comment #9) > The question is (or should be) what happens (in Excel) when you use the > TRANSPOSE() function as a normal array function (introduced with CSE), as > oppose to using it as a dynamic array function. Calc does not support > dynamic arrays features. > > Would you please test the case with TRANSPOSE() function as normal array? > What error (if any) Excel shows in such case? How can the TRANSPOSE() function be tested with a normal array? Would you please advise what needs to be typed into Excel to answer your question? Or attach a Calc spreadsheet with the answer to be opened with Excel. Thank you
(In reply to Óvári from comment #10) > Would you please advise what needs to be typed into Excel to answer your > question? Please read the following article and carefully follow the steps in it. <https://support.microsoft.com/en-au/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027> HTH.
Created attachment 191829 [details] Excel TRANSPOSE array defective (In reply to ady from comment #9) > (In reply to Óvári from comment #8) > > #SPILL! > > > > Spill range it too big > > The question is (or should be) what happens (in Excel) when you use the > TRANSPOSE() function as a normal array function (introduced with CSE), as > oppose to using it as a dynamic array function. Calc does not support > dynamic arrays features. > > Would you please test the case with TRANSPOSE() function as normal array? > What error (if any) Excel shows in such case? Microsoft Excel normal array function is defective.