Re: [libreoffice-users] Calc- How to count selected lines

[libreoffice-ml . mbourne]

Rob Jasper wrote:

Pedro,

In the 'derived' view in the second tab (example 2) the empty fields under 
heading Nr do contain a formula to display the number only if something is 
specified in the 'map' column in the main list (example 1).
COUNTA also counts cells when they only contain a formula that returns an empty 
string. So, the effect is that it will count all cells.
COUNT only counts cells containing numeric values
I couldn't find how to use COUNTIF to do what I want. I always seem to have 
troibles with the formatting...
I tried:
COUNTIF(D$4:D4,"<> ")
COUNTIF(D$4:D4,'<>" "')##   
COUNTIF)D$4:D4,"<>''") ## string delimited with single quotes, inside total 
condition withing double quotes

I just found 2 formats that seem to work:
COUNTIF(D$4:D4,"<>"&"")

and also
COUNTIF(D$4:D4,"<>")
No clue why this works, but it does...


You can use expressions like COUNTIF(D$4:D4,">2") to count cells with a 
value greater than 2.


There are two main conventions I've come across in programming languages 
for "not equals" - "<>" or "!=". COUNTIF seems to use "<>" - you can use 
an expression like COUNTIF(D$4:D4,"<>2") to count cells with a value not 
equal to 2, or COUNTIF(D$4:D4,"<>Hello") to count cells with a value not 
equal to "Hello".


I guess COUNTIF(D$4:D4,"<>") counts all cells not equal to blank.

I'd never thought of that. In the past, I've used COUNTIF(D$4:D4,".+"), 
which requires Tools > Options > Calc > Calculate > "Enable regular 
expressions in formulae" to be enabled. ".+" is a regular expression 
matching cells which contain one or more characters. It looks like I've 
been overlooking an easier (and probably efficient) solution, so thanks 
for mentioning that.


--
Mark.


--
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: Calc- How to count selected lines

[Pedro]

Hi Rob


Rob wrote
> I just found 2 formats that seem to work:
> COUNTIF(D$4:D4,"<>"&"")
> 
> and also
> COUNTIF(D$4:D4,"<>")
> No clue why this works, but it does...
> 
> Thanks for thinking with me,

I really don't understand why it works but it does.
I'm attaching a sample file in case someone finds this useful.
COUNTIF.ods   

Thank you for teaching me a new trick. This formula is going to be quite
handy.

Best regards,
Pedro



--
View this message in context: 
http://nabble.documentfoundation.org/Calc-How-to-count-selected-lines-tp4179893p4179920.html
Sent from the Users mailing list archive at Nabble.com.

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Calc- How to count selected lines

[Rob Jasper]

Pedro,

In the 'derived' view in the second tab (example 2) the empty fields under 
heading Nr do contain a formula to display the number only if something is 
specified in the 'map' column in the main list (example 1). 
COUNTA also counts cells when they only contain a formula that returns an empty 
string. So, the effect is that it will count all cells. 
COUNT only counts cells containing numeric values
I couldn't find how to use COUNTIF to do what I want. I always seem to have 
troibles with the formatting...
I tried:
COUNTIF(D$4:D4,"<> ")
COUNTIF(D$4:D4,'<>" "')##   
COUNTIF)D$4:D4,"<>''") ## string delimited with single quotes, inside total 
condition withing double quotes

I just found 2 formats that seem to work:
COUNTIF(D$4:D4,"<>"&"")

and also
COUNTIF(D$4:D4,"<>")
No clue why this works, but it does...

Thanks for thinking with me,

Rob.


On 30 mrt. 2016, at 17:29, Pedro wrote:

> I'm not sure if this helps with any of your questions but COUNTA counts
> numbers and/or text
> 
> If you can provide a link to a sample file and explain what you expect maybe
> it is easier.
> 
> Pedro
> 
> 
> 
> --
> View this message in context: 
> http://nabble.documentfoundation.org/Calc-How-to-count-selected-lines-tp4179893p4179898.html
> Sent from the Users mailing list archive at Nabble.com.
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@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
> 


-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Re: Calc- How to count selected lines

[Pedro]

I'm not sure if this helps with any of your questions but COUNTA counts
numbers and/or text

If you can provide a link to a sample file and explain what you expect maybe
it is easier.

Pedro



--
View this message in context: 
http://nabble.documentfoundation.org/Calc-How-to-count-selected-lines-tp4179893p4179898.html
Sent from the Users mailing list archive at Nabble.com.

-- 
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Calc- How to count selected lines

[Rob Jasper]

Hi all,

maybe I have a blind spot, but I don't see the solution..

I have a long list (in my case of music numbers, with titles, dates, and admin 
stuff.

it looks like:

Nr  Sub ID  Pgs Beg Map Aktie   Titel
1   
1   



Volk'ren, hoort!
2   
2   



Heer, wees mijn Gids
3   
3   1   


De waldhoorn
4   
4   1   


O Heer, die daar (Val.Gedenckklank)
5   A   5A  2   


Ei oechajem
5   B   5B  1   


Soldatenlied
6   
6   2   


Die zwölf Räuber
7   
7   



0 bone Jesu
8   
8   1   


Nu jubelt en wees blij
9   
9   2   


Het Gebed (zie 162)
10  
10  1   


Wat de toekomst brengen moge
11  A   11A 1   
19-10   i   Stille Nacht
12  A   12A 1   
19-10   i   Bethlehem
12  B   12B 2   


Ik kniel aan Uw kribbe neer
13  
13  1   
5-01i   Piet Hein (de Zilvervloot)
14  
14  2   


Sanctus (uit de Deutsche Messe V)
15  
15  1   P   

Ambrosianischer Lobgesang

Now I have an other tab where I select only things with a value in the "Map" 
column

1   24-08-15242 Jacob's Ladder
2   M   275 Dicht bij het hart van God
3   M   279 Geduchte God hoor mijn gebeden
4   M   305 Abba Vader
5   24-08-15363 Zie de Zon
6   05-01-15369 Heer mijn hart zoekt u te vinden
7   24-03-14396 Geef aan de wereld vrede
7   04-04-16433SAan de Voorthuizense dreven
8   M   453 Dank zij U Heer

I use the fomula 
=IF(AND(OR(Lijst.$F2="M",ISNUMBER(Lijst.$F2)),NOT(Lijst.$G2="i")),Lijst.$C2,"") 
in the third column. 
Subsequently I use a filter to just filter anything with a value in the 3rd 
column.

Question 1:
Is there a more clever way of doing this, so that I don't need the manual 
action to adapt the filter if something changes in the main list?

Question 2:
I want a sequence number in the first column- I started out with using 
SUBTOTAL(2,) for this. This didn't work since not all values are numeric 
(e.g. 433S). 
COUNT(D$4:D..) has the same problem as can be seen in above example (twice nr 7)

Any suggestions?
thanks,
Rob.






-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Calc- How to count selected lines

[Rob Jasper]

The table was really messed up- So here it is in text:

ID  Pgs Beg Map Aktie   Titel
1   Volk'ren, hoort!
2   Heer, wees mijn Gids
3   1   De waldhoorn
4   1   O Heer, die daar (Val.Gedenckklank)
5A  2   Ei oechajem
5B  1   Soldatenlied
6   2   Die zwölf Räuber
7   0 bone Jesu
8   1   Nu jubelt en wees blij
9   2   Het Gebed (zie 162)
10  1   Wat de toekomst brengen moge
11A 1   19-10   i   Stille Nacht
12A 1   19-10   i   Bethlehem
12B 2   Ik kniel aan Uw kribbe neer
13  1   5-01i   Piet Hein (de Zilvervloot)
14  2   Sanctus (uit de Deutsche Messe V)
15  1   P   Ambrosianischer Lobgesang
The second looks like:

Seq Datum   Nr  Titel
1   24-08-15242 Jacob's Ladder
2   M   275 Dicht bij het hart van God
3   M   279 Geduchte God hoor mijn gebeden
4   M   305 Abba Vader
5   24-08-15363 Zie de Zon
6   05-01-15369 Heer mijn hart zoekt u te vinden
7   24-03-14396 Geef aan de wereld vrede
7   04-04-16433SAan de Voorthuizense dreven
8   M   453 Dank zij U Heer


On 30 mrt. 2016, at 16:30, Rob Jasper wrote:

> Hi all,
> 
> maybe I have a blind spot, but I don't see the solution..
> 
> I have a long list (in my case of music numbers, with titles, dates, and 
> admin stuff.
> 
> it looks like:
> 
> NrSub ID  Pgs Beg Map Aktie   Titel
> 1 
> 1 
> 
> 
> 
> Volk'ren, hoort!
> 2 
> 2 
> 
> 
> 
> Heer, wees mijn Gids
> 3 
> 3 1   
> 
> 
> De waldhoorn
> 4 
> 4 1   
> 
> 
> O Heer, die daar (Val.Gedenckklank)
> 5 A   5A  2   
> 
> 
> Ei oechajem
> 5 B   5B  1   
> 
> 
> Soldatenlied
> 6 
> 6 2   
> 
> 
> Die zwölf Räuber
> 7 
> 7 
> 
> 
> 
> 0 bone Jesu
> 8 
> 8 1   
> 
> 
> Nu jubelt en wees blij
> 9 
> 9 2   
> 
> 
> Het Gebed (zie 162)
> 10
> 101   
> 
> 
> Wat de toekomst brengen moge
> 11A   11A 1   
> 19-10 i   Stille Nacht
> 12A   12A 1   
> 19-10 i   Bethlehem
> 12B   12B 2   
> 
> 
> Ik kniel aan Uw kribbe neer
> 13
> 131   
> 5-01  i   Piet Hein (de Zilvervloot)
> 14
> 142   
> 
> 
> Sanctus (uit de Deutsche Messe V)
> 15
> 151   P   
> 
> Ambrosianischer Lobgesang
> 
> Now I have an other tab where I select only things with a value in the "Map" 
> column
> 
> 1 24-08-15242 Jacob's Ladder
> 2 M   275 Dicht bij het hart van God
> 3 M   279 Geduchte God hoor mijn gebeden
> 4 M   305 Abba Vader
> 5 24-08-15363 Zie de Zon
> 6 05-01-15369 Heer mijn hart zoekt u te vinden
> 7 24-03-14396 Geef aan de wereld vrede
> 7 04-04-16433SAan de Voorthuizense dreven
> 8 M   453 Dank zij U Heer
> 
> I use the fomula 
> =IF(AND(OR(Lijst.$F2="M",ISNUMBER(Lijst.$F2)),NOT(Lijst.$G2="i")),Lijst.$C2,"")
>  in the third column. 
> Subsequently I use a filter to just filter anything with a value in the 3rd 
> column.
> 
> Question 1:
> Is there a more clever way of doing this, so that I don't need the manual 
> action to adapt the filter if something changes in the main list?
> 
> Question 2:
> I want a sequence number in the first column- I started out with using 
> SUBTOTAL(2,) for this. This didn't work since not all values are 
> numeric (e.g. 433S). 
> COUNT(D$4:D..) has the same problem as can be seen in above example (twice nr 
> 7)
> 
> Any suggestions?
> thanks,
> Rob.
> 
> 
> 
> 
> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@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
> 


-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Awkward behaviour of LO Calc

[Mike Scott]

On 30/03/16 14:37, Mike Scott wrote:



Excel is documented as Calc's order, ie the opposite.


And MS are inconsistent - VB puts exponentiation above unary -
(https://msdn.microsoft.com/en-us/library/fw84t893.aspx)
opposite to Excel. Oh well :-)




--
Mike Scott (unet2  [deletethis] scottsonline.org.uk)
Harlow Essex England

--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Awkward behaviour of LO Calc

[Mike Scott]

On 30/03/16 13:52, Alex McMurchy wrote:

Unless I'm misremembering my maths  -2² would be +4 and -(2²) would be -4

So in my mind no bug.


Just a matter of convention. Unfortunately where there's one, there's 
another...


It seems that perl, python and php differ. Perl and python I've tried 
and it's so, php's manual puts exponentiation at higher precedence than 
unary - (http://php.net/manual/en/language.operators.precedence.php) 
although the ** operator is new in php 5.6 so I can't check offhand.


eg
mike@spock> python
Python 2.7.6 (default, Jun 22 2015, 17:58:13)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> print -3 ** 2
-9


Excel is documented as Calc's order, ie the opposite.

Javascript seems to agree with Excel.
(https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Operators/Operator_Precedence)

Fortran (remember that?) puts - below **
(https://www.fortran.com/F77_std/rjcnf0001-sh-6.html)
that page says explicitly
"For example, in the expression

- A ** 2

the exponentiation operator (**) has precedence over the negation 
operator (-)  The interpretation of the above expression is the same 
as the interpretation of the expression


- (A ** 2)
"


Oddly, I can't find any strictly /mathematical/ guideline! Anyone have 
the maths students' guide to operator precedence?



It looks as though opinion is divided.


I'd suggest always using brackets to be sure and clear. In any case, it 
really needs documenting for Calc.




--
Mike Scott (unet2  [deletethis] scottsonline.org.uk)
Harlow Essex England

--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Awkward behaviour of LO Calc

[Alex McMurchy]

Unless I'm misremembering my maths  -2² would be +4 and -(2²) would be -4

So in my mind no bug.

Alex

On 29/03/16 09:38, lorieul wrote:

Hello to all,

I have found a fancy behavior of Calc but I cannot decide whether it is
a bug or a "feature". Hence I was looking for your advice before posting
a bug report (if required).

   1. Open LO Calc
   2. In A1 enter formula "=1-2^2" and press enter key
  The result shown is -3 which is ok since
  1-2² = 1-(2²) = 1-4 = -3
   3. In A2 enter formula "=-2^2" and press enter key
  I would expect this to be treated as -2² = -(2²) = -4
  But Calc considers it as (-2)² = +4
  Quite obviously, a work around is to define the formula as
  "=-(2^2)" which indeed returns -4

I've checked the wiki page dedicated to operators [1] but it does not
give much information on operator precedence.

 From my standpoint, both examples show an inconsistent behavior of Calc.
For me, either we should have both 1-2^2 = 1+4 = 5 and -2^2 = +4
Or we should have both 1-2^2 = 1-4 = -3 and -2^2 = -4

However, perhaps some of you have a different view point on the matter ?
In other words, is that a bug or is it perfectly normal ?

Of course changing such behavior will cause a hell lot of trouble in
existing documents… but perhaps the operator precedence should be
explicitly specified on the wiki ?

Tested both on LO 5.1.0.3 and on LO 4.2.8.2 (build ID 420m0(Build:2)).

Cheers,

Gaël Lorieul


[1] https://help.libreoffice.org/Calc/Operators_in_Calc






--
To unsubscribe e-mail to: users+unsubscr...@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

[libreoffice-users] Removed undocumented perftune.ini

[Stephan Bergmann]

FYI, 
: 
 "Support was removed for the undocumented perftune.ini flags 
FastPipeCommunication (core commit 
f8f87780f5f7be5ff185b0b8b3206d760d9dfed6) and 
QuickstartPreloadConfiguration (core commit 
d3084fb2442b9584b46fba743158c3fe46defa81)."


I assume nobody using LibreOffice was relying on those undocumented 
features that were added to OpenOffice.org/StarOffice back in the day.


--
To unsubscribe e-mail to: users+unsubscr...@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