Bug 158620

Summary: TRANSPOSE() should generate a #SPILL! error if cells available are not sufficient to write the result, i.e. Spill range is too big
Product: LibreOffice Reporter: Óvári <ovari123>
Component: CalcAssignee: Not Assigned <libreoffice-bugs>
Status: NEW ---    
Severity: normal CC: aron.budea, erack, kelemeng
Priority: medium    
Version: 7.6.4.1 release   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=126573
https://bugs.documentfoundation.org/show_bug.cgi?id=161399
Whiteboard:
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 150900    
Attachments: TRANSPOSE #SPILL!
TRANSPOSE Spill range is too big
Excel TRANSPOSE array defective

Description Óvári 2023-12-10 00:28:30 UTC 
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
Comment 1 Óvári 2023-12-10 01:32:13 UTC 
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
Comment 2 Óvári 2023-12-10 02:13:30 UTC 
Can you please change the Status to NEW?
Comment 3 ady 2023-12-10 05:21:36 UTC 
(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.
Comment 4 Eike Rathke 2023-12-11 14:09:55 UTC 
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.
Comment 5 ady 2023-12-11 16:07:54 UTC 
(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.
Comment 6 Eike Rathke 2023-12-11 17:29:57 UTC 
(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?
Comment 7 Óvári 2023-12-12 06:48:17 UTC 
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!
Comment 8 Óvári 2023-12-12 06:49:20 UTC 
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
Comment 9 ady 2023-12-12 12:08:03 UTC 
(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?
Comment 10 Óvári 2023-12-12 21:02:23 UTC 
(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
Comment 11 ady 2023-12-12 21:52:25 UTC 
(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.
Comment 12 Óvári 2024-01-09 21:34:42 UTC 
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.