regexp string comparison in array functions

[Cazaril]

Hi,

I have this little problem evaluating a table. The format of the table is basically like this:

val1 phone1 time1
val2 phone2 time2
...

I'd like to add all times, if VALx=="something" and PHONEx="1.*". Meaning: If the VALx is a certain value I know, and the phone number starts with one. I have developed the following little array formula:

==SUM((I2:I200="IWE")*(H2:H200="1.*")*((J2:J200)/86400))

Alas, it does not work. the string comparison in the 2nd part is always false, as I have found out. does anyone know how I can solve this thing? I break my head since 2 hours at least, in which I could have the whole thing done by hand several times , but I'd like to know why this fails ... . the screenshot below should illustrate the problem.



I am using neoffice newest 2.2.2 patch 2 for mac os btw., and yes, I did enable "regular expressions in formulas" in the OO options


thanks in advance for any responses!

[acknak]

Unfortunately, only a few special places will use regular expression matching, and plain string comparison ('=') is not one of them.

This works for me:

[drking]

I've had a look at using countif, which is another way to test simple comparisons with regex

=COUNTIF(B1; "^1.*" ) returns 1 or 0

Unfortunately in an array the B1:B2 is interpreted by COUNTIF, not the array processing [eg =SUMPRODUCT(COUNTIF(B1:B2; "^1.*" ); ..........) ]

Looking at search:
=SEARCH("^1.*"; B1:B2)
is (rightly or wrongly) a valid syntax, but only cell B1 will be searched. So once you stick it in an array, I wonder if under certain conditions SEARCH is interpreting the B1:B2, rather than the array processing. That might explain why your example doesn't work.

I'm sorry I haven't found a full solution yet either.

[drking]

aha!

=SUMPRODUCT((I2:I200="IWE");NOT(ISERR(SEARCH("^1.*";H2:H200)));(J2:J200)/86400)

(assuming the first term doesn't use regex)

[Cazaril]

The solution from drking with if(iserr(search(...))) does work for me, as far as I have tested. It's a shame though that the "=" operator does not support regex in this context - it's a very powerful method of evaluating data, and this makes things un-intuitive and unneccessarily hard.

Thanks for your efforts, though!!

Would this be an item for a feature request - what do you think?

[acknak]

Sure, it should be an option for any string comparison.

You can file a request here: Open Office Quality Assurance - Report Bugs

Just be sure to do a search first--someone may have already asked for it.

[drking]

I've recently trawled through almost 40 regex issues in order to write the regex HowTos, and I don't recall this feature being on the list of enhancement requests