Created attachment 49229 [details] sample file to show the reported bug Bring up the sample file. On sheet 1, the 4 areas of interest are: A1:B19 = The named range TestRange C1:D19 = The array formula {=TestRange} E1:F19 = The array formula {=ISBLANK(TestRange)} G1:H19 = The array formula {=ISBLANK(C1:D19)} You will see the bug immediately: cell G1 shows FALSE, when it should show TRUE. This same bug will also cause formulas to fail. Cell I1 contains the formula "=C1+5", which returns the error "Wrong data type".
Reproduced on LibreOffice 3.4 340m1(Build:103) for OpenSuse Linux.
I wanted to add that this bug isn't really about ISBLANK. ISBLANK is just the first function I noticed the bug with. This bug is really about how array formulas mangle empty cells, if the cell is the first one in the range. I wanted to add additional data points regarding the offending cell. SUM(<offending cell>,5) correctly returns 5 =<offending cell>+5 gives a "Wrong data type" error PRODUCT(<offending cell>,5) incorrectly returns 5!?!? =<offending cell>*5 gives a "Wrong data type" error ISTEXT() incorrectly returns TRUE ISEVEN() incorrectly returns error 504 (see ISTEXT for why). It should return TRUE. ISODD() has the same problem as ISEVEN(). AND() and OR() both incorrectly give a "Wrong data type" error. They should return FALSE. ABS() gives "Wrong data type'. It should return 0. Why do SUM() and PRODUCT() work differently than '+' and '*'? It is extremely odd that SUM() treats the offending cell as 0 and PRODUCT() treats it as 1. It's also odd that both treat the offending cell as a number, rather than giving "Wrong data type" errors.
I have another sample of array formula corruption to show you, which may be related to the original test case for this bug. I know the attached sheet is complex, but the obvious stripped down sheet doesn't seem to show the problem. I have attached the file "Corruption 2.ods" as the sample. What I'm doing is using ranges joined with the ~ operator, like so: Range1~Range2~Range3 using the multiple range mode of INDEX: INDEX((Range1~Range2~Range3),0,0,selector) I use the range/column returned by INDEX() as the key in an array VLOOKUP: VLOOKUP(INDEX(...),Table,2,0) Each row is a key. Finally, the numbers returned are all summed together in a complete array formula: ={SUM(IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)))} If LibreOffice had IFNA that formula wouldn't be so hideous to read, not to mention the redundant and wasteful double-evaluation of VLOOKUP, but I digress. On to the sheet. It should start on the sheet titled Character Data. Two cells are of interest, R2C21 and R14C26. They are notable for two things: having the exact same aforementioned array formula in them, and returning different (!) results. Just so you know, the result (3/4) displayed in R14C26 is the correct one. As indicated by the contents of R2C15 (used by the formula), we are looking up a column in range #4 passed to INDEX. You can see the contents of that column on sheet "Powers", near the column headed "Power 4" (R1C10). It has two non-empty rows, Linked and Visible. Each row of Linked is worth 1/2, each Visible 1/4, hence the correct sum of 3/4. These numbers are looked up from LimitationsTable, which is columns 25 and 26 of sheet "Computed". Columns25/26 themselves contain an array formula using the multiple range mode of INDEX. You can see the raw data on sheet Edition. Just search for Linked and you will see there are 3 mostly-redundant columns of data, any one of which could be the data returned in LimitationsTable. In the sheet as configured, it happens to be the first one. If you want to change which of the 3 is used for the lookup, change the "4th", on sheet Character Data, to either 5th or 6th. It's amusing to watch the fireworks for 1/2 a second. One more thing: this spreadsheet sometimes triggers a nasty save-corruption bug (39485), so keep an original handy.
Created attachment 49592 [details] demonstration of array formula corruption
Created attachment 49594 [details] VLOOKUP and MATCH fail
I've attached another sample, this time of VLOOKUP and MATCH failing. The spreadsheet is not visibly corrupt; all the data is verifiably there and where it's supposed to be. For the cells of interest: look at R21 and R22, C27, sheet Character Data They are, respectively, a VLOOKUP and MATCH on the same table using the same key. If you go look at the table (columns 27 and 28 of sheet Computed), you can see that the key is in the very first row. I checked that are no hidden spaces or nonprinting characters that would throw things off. The #N/A columns you see to the left are manifestations of the same problem.
[This is an automated message.] This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it started right out as NEW without ever being explicitly confirmed. The bug is changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases. Details on how to test the 3.5.0 beta1 can be found at: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
I have confirmed that this bug persists in LibreOffice 3.5 Beta 2 for Mac (OSX 10.6.8).
(In reply to comment #1) > Reproduced on LibreOffice 3.4 340m1(Build:103) for OpenSuse Linux. This means that the Platform can not be just “Mac OS”, therefore I change it to “All” as appropriate (even if this bug was not reproducible on Windows, we need to use “All” if more than one Platform is affected).
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (4.3.5 or later): https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) Thank you for your help! -- The LibreOffice QA Team
Confirmed. Win 7 Pro 64-bit Version: 4.5.0.0.alpha0+ Build ID: 07e84cae983c08afdba03018413a19d01abb3006 TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-01-19_06:15:38
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.