Re: Possible Formula Bug in Calc? (IsError and Find)

[ouch]

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 
 wrote:
 

 #yiv3704922496 #yiv3704922496 -- _filtered #yiv3704922496 {panose-1:2 4 5 3 5 
4 6 3 2 4;} _filtered #yiv3704922496 {font-family:Calibri;panose-1:2 15 5 2 2 2 
4 3 2 4;}#yiv3704922496 #yiv3704922496 p.yiv3704922496MsoNormal, #yiv3704922496 
li.yiv3704922496MsoNormal, #yiv3704922496 div.yiv3704922496MsoNormal 
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;}#yiv3704922496 a:link, 
#yiv3704922496 span.yiv3704922496MsoHyperlink 
{color:blue;text-decoration:underline;}#yiv3704922496 a:visited, #yiv3704922496 
span.yiv3704922496MsoHyperlinkFollowed 
{color:#954F72;text-decoration: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 
4-10-2017'.$A$1:$A$1000;SMALL(IF(ISERROR(FIND("Discount";$'Sales 
4-10-2017'.$A$1:$A$1000))=0;ROW($'Sales 
4-10-2017'.$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?    

   

RE: Possible Formula Bug in Calc? (IsError and Find)

[Howard Cary Morris]

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 
4-10-2017'.$A$1:$A$1000;SMALL(IF(ISERROR(FIND("Discount";$'Sales 
4-10-2017'.$A$1:$A$1000))=0;ROW($'Sales 
4-10-2017'.$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?

Re: Possible Formula Bug in Calc? (IsError and Find)

[Regina Henschel]

Hi,

The purpose of FIND is to get the position of a substring in a longer 
string. What do you want to achieve? The use of FIND in an array 
function looks strange.


Kind regards
Regina

ouch schrieb:

I have an array formula that is returning an error of #VALUE when it 
shouldn't.The formula is below.
{=ISERROR(INDEX($'Sales 4-10-2017'.$A$1:$A$1000;SMALL(IF(ISERROR(FIND("Discount";$'Sales 
4-10-2017'.$A$1:$A$1000))=0;ROW($'Sales 4-10-2017'.$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?




-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org