Re: [libreoffice-users] Calc formula syntax assistance

At 19:42 19/12/2015 +0200, Hylton Conacher wrote:
> Running :O 5.0.3.2 on IMac and have seen there 
> is a 5.0.4 update, however, perhaps my issue does not require an update.
>
> Anyway, the formula involves COUNTIF with the 
> search range on another sheet and the search 
> criteria in a cell adjacent to the countif formula.
> =COUNTIF(Data.$C$4:C$492,$B17)
> Each search criteria (B17), is text consisting 
> of spaces, numbers and special characters [e.g.] BLUE GUM LANE (NO'S 1 - 17)
>
> On my version of LO this formula displays a zero 
> as the count of the search criteria however on 
> reviewing the Data sheet, I can identify at 
> least one exact replica within range, yet it is 
> not counted? Any ideas to get the correct answer 
> of the number of times a field appears?

There are simple ways to debug problems such as 
this - which you can do with your actual 
spreadsheet but you deprive anyone offering to help you from doing!
o Set up a model spreadsheet with very simple 
entries so that something works - to establish 
that you understand exactly what COUNTIF() does.
o Make a copy of your real spreadsheet and 
simplify it - removing material and simplifying 
the data - until it works. Whatever you changed 
last to make the function spring into life will be the culprit.

As the help text explains about COUNTIF(), "[t]he 
search supports regular expressions." With 
regular expression enabled, the parentheses in 
your example data are interpreted as marking off 
the enclosed text as a reference. This can be 
referred to later in a search string (or in a 
replacement string, where relevant). This means 
that the parentheses are punctuation within the 
search string and not part of it: your example 
string would match "BLUE GUM LANE NO'S 1 - 17" 
but not "BLUE GUM LANE (NO'S 1 ­ 17)".

If this is the problem, there are two alternative 
techniques to solve it. Either:
o Disable regular expressions in formulae by 
removing the tick from Tools | Options... | 
LibreOffice Calc | Calculate | Enable regular 
expressions in formulae. (That will be Preferences... on the Mac, won't it?)
Or:
o Escape the parentheses by preceding them by 
backslashes in the search string: BLUE GUM LANE 
\(NO'S 1 - 17\) - so that they are treated as 
nothing more than the simple text characters you mean them to be.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted