Created attachment 76026 [details] xlsx file showing incorrect array formula My test case (see attached file) was created in LibreOffice 3.5.7.2 running under Ubuntu 10.04. However, a friend has confirmed it also happens with the most recent versions of LibreOffice and Ubuntu. Steps to reproduce: 1. Enter a 2x3 array in cells A1:B3. 2. Enter a 3x1 array in cells E1:E3. 3. Enter array formula MMULT(A1:B3;E1:E3) in cells E5:E6. 4. Save as test.ods. Reopen, and everything is fine. 5. Save as test.xlsx. Reopen, and the entries in cells E5:E6 are incorrect. The value in E5 is correct, but it is no longer an array formula. The value in E6 is incorrect, since it is just =E5. The test.xlsx file is attached below.
Comment on attachment 76026 [details] xlsx file showing incorrect array formula Note: I have updated the attachment's mime type so that downloading is possible
I could not reproduce it with LO 4.0.1.2 (Win7 Home, 64bit). But is this a misunderstanding from my side, what should be inserted in cell E6? Does this issue still persist for you with LO 4.0.1.2?
LO 4.0.1.2 does not fix this issue for me (running on ubuntu 12.04), though there is a slight change compared to LO 3.5.7.2. Let me give you the specifics of my example again: Cell A1: 1 Cell B1: 2 Cell C1: 3 Cell A2: 4 Cell B2: 5 Cell C2: 6 Cells E1:E3 2.8, 3.5 and 4.7 respectively. Save as .ods, and re-open: values in cells E5 and E6 and 23.9 and 56.9 respectively. For either cell E5 or cell E6, the formula bar displays (correctly) {=MMULT(A1:C2,E1:E3)} This was fine in both LO 3.5.7.2 and LO 4.0.1.2. Save as .xlsx, and re-open: 1. Under LO 3.5.7.2, values in both cells E5 and E6 were 23.9. Formula bar gives =MMULT(A1:C2,E1:E3) (note the absence of curly braces) in cell E5, and =E5 in E6. 2. Under LO 4.0.1.2, values in cells E5 and E6 are 23.9 and 56.9 respectively. However, the formula bar gives the same behaviour as under LO 3.5.7.2, indicating that this is no longer an array formula (due to the absence of curly braces). Moreover, suppose I change the entry in cell C2 from 6 to 7. This should have no effect on the value in cell E5, but it should change the value in cell E6 to 61.6. This happens for the .ods file. However, making this change in the .xlsx file, the entry in cell E6 changes to 23.9. In other words, as soon as the spreadsheet recalculates, the entry is incorrect under LO 4.0.1.2. On 03/30/2013 04:36 AM, bugzilla-daemon@freedesktop.org wrote: > > *Comment # 2 <https://bugs.freedesktop.org/show_bug.cgi?id=61908#c2> > on bug 61908 <https://bugs.freedesktop.org/show_bug.cgi?id=61908> from > A <mailto:stgohi-lobugs@yahoo.de> * > I could not reproduce it with LO 4.0.1.2 (Win7 Home, 64bit). > But is this a misunderstanding from my side, what should be inserted in cell > E6? > > Does this issue still persist for you with LO 4.0.1.2? > ------------------------------------------------------------------------ > You are receiving this mail because: > > * You reported the bug. >
LO 4.0.1.2 does not fix this issue for me, running on Windows 7.
It's nothing to do with the actual MMULT function, it's to do with the array formulas. All array formulas fail when saved to xlsx.
Thank you for reporting this issue! I have been able to confirm the issue on: Version: 4.0.3.3 release Platform: Bodhi Linux 2.2 x64 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + As I've been able to confirm this problem I am marking as: New (confirmed) Major - loss of data High - default, seems appropriate although a case for highest could probably be made as arrays are essentially useless with probably the most common format around these days (I believe xlsx is surpassing or has surpassed xls format) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link: https://wiki.documentfoundation.org/QA/BugTriage and join us on freenode at #libreoffice-qa There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/. Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
*** Bug 61157 has been marked as a duplicate of this bug. ***
Check also file in: https://bugs.freedesktop.org/show_bug.cgi?id=65835 with alot of arrays formula.
I also confirm using 4.3.3.2 on Ubuntu linux. Attaching a slightly different test case which just uses SUM(range-range)
Created attachment 114045 [details] Another simple test case
Created attachment 114047 [details] Another simple test case with embedded instructions
Created attachment 114048 [details] Another simple test case with embedded instructions
- This is not right way to calculate matrix multiplication. If array A1 = R1 x C1 and array A2 = R2 x C2 (R = row and C = Column) then the resultant matrix(there must be C1=R2) will be R1 x C2, but here it allocate only one cell for matrix multiplication result instead of two. - Please refer this link for more details : http://www.excelfunctions.net/Excel-Mmult-Function.html - Suppose if we allocate single cell for array matrix multiplication, resultant matrix will display the first row multiplication with first column which is correct.(i.e G5 in our case.) And if we make G6 formula as equal to G5 which shows right result in LibreOffice. - Thus, there is problem while exporting MMULT() i.e matrix multiplication.
Created attachment 116220 [details] Array multiplication example
Created attachment 116223 [details] MMULT-RT.xlsx
yogesh.bharate001 committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=beb8e2830dc9e1c771e196fcaf08cdfd6bf3dde3 tdf#61908:XLSX formula cell range is not exported for MMULT. It will be available in 5.1.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.
*** Bug 94196 has been marked as a duplicate of this bug. ***
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=92df7db85a3da10f18a5a06fb53a9cb69910e835 array formulas do not consist only of multiple rows, tdf#61908 follow-up It will be available in 5.1.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f501fe4da88e1d64fcc88a492a52911113d28f6a do not write MM_REFERENCE formulas to OOXML, tdf#61908 follow-up It will be available in 5.1.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.
Squashed patches and an additional fix pending review https://gerrit.libreoffice.org/18592 for 5-0 https://gerrit.libreoffice.org/18593 for 4-4
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=464d1ef1868ce879e1bd7542167df8e4b9c68d1e&h=libreoffice-4-4 tdf#61908 OOXML export cell range for matrix/array formula It will be available in 4.4.6. 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-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=225bd5f46367ef0955c5cf5037a7f31cd8da3adc&h=libreoffice-5-0 tdf#61908 OOXML export cell range for matrix/array formula It will be available in 5.0.3. 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.