Apache OpenOffice (AOO) Bugzilla – Issue 118236
ISBLANK() returns FALSE instead of #VALUE!
Last modified: 2024-01-16 08:00:33 UTC
First an example of correct behaviour using ABS(): Create new blank spreadsheet In cell A1 enter =ABS(B1:B2) In cell A2 enter =ABS(B1:B2) In cell A3 enter =ABS(B1:B2) Result: A1=0, A2=0, A3=#VALUE! (this is correct) Now an example of failure with ISBLANK(): Create new blank spreadsheet In cell A1 enter =ISBLANK(B1:B2) In cell A2 enter =ISBLANK(B1:B2) In cell A3 enter =ISBLANK(B1:B2) Result: A1=TRUE, A2=TRUE, A3=FALSE. Problem: A3 should be #VALUE! because the target range is invalid. Note: Excel is reported to have the same behaviour, but that does not make it correct. :p
Standard is, "Summary: Return TRUE if the referenced cell is blank, else return FALSE" It does not state that an error value is not propagated, but 'else' can be read to cover all other cases including error values. Gnumeric returns FALSE too. So I see no need to change the behavior. The ODF1.2 draft is in public comment state. You might report to OASIS, that the specification is unclear for the error value case.
http://www.oasis-open.org/news/announcements/60-day-public-review-for-opendocument-version-1-2-cos
getting rid of value "enhancement" for field "severity". For enhancement the field "issue type" shall be used.
I'm missing something here. Why is the range B1:B2 invalid when references from cell A3, but not when references from A1 or A2? It is the same range either way, yes?
Results of description are obtained with Calc 4.1 and Excel 2010. IMHO true correct behavior should not allow cell range for the functions ABS and ISBLANK. These functions are intended according both Calc and Excel help to apply on "a cell" - not plural. AOO410m1(Build:9750) - Rev. 1543812 Rev.1543812 Win 7
Version has been modified erroneous, so back to most early version
ODF 1.3 added "This function does not propagate Error values." to the ISBLANK, ISFORMULA, ISLOGICAL, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, and TYPE functions. OpenOffice already does the right thing. Should we resolve this as WONT_FIX?
(In reply to damjan from comment #7) > ODF 1.3 added "This function does not propagate Error values." to the > ISBLANK, ISFORMULA, ISLOGICAL, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, and TYPE > functions. OpenOffice already does the right thing. Does it matter that the TYPE function returns #VALUE!- as shown in the attached .ods file? > > Should we resolve this as WONT_FIX? +1 as far as ISBLANK() is concerned. BTW: Just wondering whether AOO Help and Calc Guide should point out what Calc functions do not (or do) return an error if: - there is no intersection (range - formula) or - the range at the intersection covers several rows or columns.
Created attachment 87207 [details] a test case for the TYPE function
(In reply to Czesław Wolański from comment #8) > (In reply to damjan from comment #7) > > ODF 1.3 added "This function does not propagate Error values." to the > > ISBLANK, ISFORMULA, ISLOGICAL, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, and TYPE > > functions. OpenOffice already does the right thing. > Does it matter that the TYPE function returns #VALUE!- as shown > in the attached .ods file? Yes it might. What do Excel, LO, Gnumeric do? And how does that .ods file work? What kind of range is "aRef" when it's not listed under Data -> Select Range. Why do the values in each column differ despite having the same formula? Why does it say aRef is A2:A3 when cell O1 has "aRef" in it? > BTW: > Just wondering whether AOO Help and Calc Guide should point out > what Calc functions do not (or do) return an error if: > - there is no intersection (range - formula) or > - the range at the intersection covers several rows or columns. LO did not change its documentation, but we might consider it.
(In reply to damjan from comment #10) > > Does it matter that the TYPE function returns #VALUE!- as shown > > in the attached .ods file? > > Yes it might. What do Excel, LO, Gnumeric do? Don't know/use Gnumeric. OpenOffice: #VALUE! LibreOffice: #VALUE! Excel 2010: 16 Excel Online: 64 > And how does that .ods file work? What kind of range is "aRef" > when it's not > listed under Data -> Select Range. "aRef" was created via Insert -> Names -> Define. >Why do the values in each column differ > despite having the same formula? The values do not differ in _each_ column. Do you mean the columns with the #VALUE! error or all columns with values that differ? > Why does it say aRef is A2:A3 when cell O1 > has "aRef" in it? The first row acts as a header and only provides information about what was used in the formulae. I should have formatted that row as text first. Apologies for any confusion caused. You'll shall get the same result whether the formulae use a range, a name (Insert -> Names -> Define) or a database range (Data -> Define Range).
(In reply to Czesław Wolański from comment #11) > (In reply to damjan from comment #10) > > > Does it matter that the TYPE function returns #VALUE!- as shown > > > in the attached .ods file? > > > > Yes it might. What do Excel, LO, Gnumeric do? > > Don't know/use Gnumeric. > > OpenOffice: #VALUE! > LibreOffice: #VALUE! > Excel 2010: 16 > Excel Online: 64 Gnumeric: 16, but the "ISFORMULA()" column evaluates to "#REF!" for all cells. Google Sheets: 16 Apple's "Numbers": doesn't support the TYPE() function. > > And how does that .ods file work? What kind of range is "aRef" > > when it's not > listed under Data -> Select Range. > > "aRef" was created via Insert -> Names -> Define. Thank you. I had no idea that feature existed. > >Why do the values in each column differ > > despite having the same formula? > > The values do not differ in _each_ column. > Do you mean the columns with the #VALUE! error or > all columns with values that differ? I thought "=aRef" was referring to column O which has "aRef" in O1, but apparently it doesn't, the defined name takes precedence. So what do we do now? Leave it as is, or make it return 16?
(In reply to damjan from comment #12) > > So what do we do now? Leave it as is, or make it return 16? It looks like - toutes proportions gardées - a million-dollar question... Reading all the documentation [1] has left me in a state of legitimate / reasoned confusion. If I understand correctly the rationale behind the entries in ODF spec v1.3: 6.3.3 Implied intersections "If a single cell is referenced; return it; otherwise, return an Error." 6.13.33 TYPE "This function does _not_ propagate Error values." and in AOO Help, the TYPE() function: "If an error occurs, the function returns a logical or numerical value." I would opt for making the TYPE() function return 16 (value 16 = error value), "unless the context otherwise requires". ;‑) SIDE NOTE: Err:502 in cells: I2 and I3 (attached .ods file). LO, Google Sheets and Excel 2010 return 1. ------------------------ [1] - Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format - "Documentation/How Tos/Using Arrays" https://wiki.openoffice.org/wiki/Documentation/How_Tos/Using_Arrays#Functions_not_expecting_array_parameters - "Documentation/How Tos/Calc: TYPE function" https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_TYPE_function - AOO Calc Help topic "Array Functions", section "Using Array Formulas in OpenOffice Calc" topic "Information Functions", section "TYPE" - their counterparts in the LO 7.6 Help Calc - LO 7.6 Calc Guide (p. 293) the "Implicit intersection of array formulas" section