Here is an example spreadsheet showing off the bug. After experimenting a bit today I can conclude
there doesn't seem to be a single function that can capture the error from Find() when used
in an array like this.
Using the function wizard and moving the cursor through the formula on the lower center table
where an error is seems to be the most telling that something is wrong. It shows the iserror()
next to index() is returning true yet the if statement it is in is running the false formula.
https://sites.google.com/site/ouchsdownloads/home/downloads/Bug%20Proof.ods
I've been using OpenOffice for years so I feel I have a pretty good handle on how it works
behind the scenes. So unless I'm overlooking something it really seems like a bug to me.
On Tuesday, April 11, 2017 1:58 PM, Howard Cary Morris <howard_cary_morris@hotmail.com>
wrote:
#yiv3704922496 #yiv3704922496  _filtered #yiv3704922496 {panose1:2 4 5 3 5 4 6 3 2 4;}
_filtered #yiv3704922496 {fontfamily:Calibri;panose1:2 15 5 2 2 2 4 3 2 4;}#yiv3704922496
#yiv3704922496 p.yiv3704922496MsoNormal, #yiv3704922496 li.yiv3704922496MsoNormal, #yiv3704922496
div.yiv3704922496MsoNormal {margin:0in;marginbottom:.0001pt;fontsize:11.0pt;}#yiv3704922496
a:link, #yiv3704922496 span.yiv3704922496MsoHyperlink {color:blue;textdecoration:underline;}#yiv3704922496
a:visited, #yiv3704922496 span.yiv3704922496MsoHyperlinkFollowed {color:#954F72;textdecoration:underline;}#yiv3704922496
.yiv3704922496MsoChpDefault {} _filtered #yiv3704922496 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv3704922496
div.yiv3704922496WordSection1 {}#yiv3704922496 I am not sure, but if you expect Find to return
a number, try N(FIND(…)) instead – it converts value into a number. Howard From:
ouch
Sent: Tuesday, April 11, 2017 2:31 AM
To: dev@openoffice.apache.org
Subject: Possible Formula Bug in Calc? (IsError and Find) I have an array formula that
is returning an error of #VALUE when it shouldn't.The formula is below.
{=ISERROR(INDEX($'Sales 4102017'.$A$1:$A$1000;SMALL(IF(ISERROR(FIND("Discount";$'Sales 4102017'.$A$1:$A$1000))=0;ROW($'Sales
4102017'.$A$1:$A$1000);"");ROW(A200))))}
Digging into it, it seems Find is culprit of the #Value error. However that initial iserror
is not catching the error and the error propagates throughout the formula even overriding
the error #504 on index caused by the Find function resulting in an invalid row being returned
due to the error.
I put that final iserror around the index function just to show that something is not right.
The formula still returns #VALUE with that on there when it obviously should be returning
either true or false regardless of what the rest of the formula is doing.
What is strange is if you move the cursor through the formula in the function wizard it gets
the expected results of true or false. But on the actual spreadsheet you get that #VALUE error.
Oh, I just tried switching out Find for Search and the same thing occurs. So maybe it's a
bug in iserror as it's the only constant?
