openoffice-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Regina Henschel <>
Subject Re: Possible Formula Bug in Calc? (IsError and Find)
Date Tue, 11 Apr 2017 07:53:12 GMT

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

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:
For additional commands, e-mail:

View raw message