Re: [libreoffice-users] Problem with the sumif function

[John R. Sowden]

thank you both.  I checked regular expressions, and it immediately 
worked.  The interesting part is I would not have "unchecked" it, and it 
previously worked.  I wonder if there was an upgrade issue, but as for 
me, now, "IT WORKS!"


thank you'

john



On 03/19/2018 01:52 PM, John R. Sowden wrote:
I had this working, then I noticed that it is no longer working (no 
error messages).


Sums any cells in col F if there is a 2211 2212 or 2213 in col M:

=SUMIF(M6:M351,"2211|2212|2213",F6:F351)

it now gives a result of zero.

I tried changing the format of the searched col to text or general 
number, no change


I have set the justification to 'centered'.  Since the title is 
greater that the width of the 4 characters,


there is a space before and after the 4 characters, but I think this 
should be for display only, name changing the data.


Turning off centered justification, etc. makes no difference.

Help?

John






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

Re: [libreoffice-users] Problem with the sumif function

[Johnny Rosenberg]

2018-03-19 22:48 GMT+01:00 Johnny Rosenberg :

> 2018-03-19 21:52 GMT+01:00 John R. Sowden :
>
>> I had this working, then I noticed that it is no longer working (no error
>> messages).
>>
>> Sums any cells in col F if there is a 2211 2212 or 2213 in col M:
>>
>> =SUMIF(M6:M351,"2211|2212|2213",F6:F351)
>>
>
> Are you sure this is supposed to work? I can't find any examples like this.
>
> However, this works:
> =SUMIF(M6:M351,2211,F6:F351) + SUMIF(M6:M351,2212,F6:F351) +
> SUMIF(M6:M351,2213,F6:F351)
>
> This also works:
> {=SUM(SUMIFS(F6:F351;M6:M351;{2211;2212;2213}))}
> (it's a matrix formula, so enter everything except the surrounding {},
> then hit Ctrl+Shift+Enter rather than only Enter)
>

Oops… I meant it's an ARRAY formula. It's ”matrisformel” in my language, so
I accidently translated ”matris” to ”matrix”, which seems like a good idea,
but there are a lot of words that look like they mean something while they
really mean something quite different… for instance, my language's word
”semester” means vacation and there are a few more examples of words that
also likely could cause some confusion… :P



Kind regards

Johnny Rosenberg


>
>
> Kind regards
>
> Johnny Rosenberg
>
>
>> it now gives a result of zero.
>>
>> I tried changing the format of the searched col to text or general
>> number, no change
>>
>> I have set the justification to 'centered'.  Since the title is greater
>> that the width of the 4 characters,
>>
>> there is a space before and after the 4 characters, but I think this
>> should be for display only, name changing the data.
>>
>> Turning off centered justification, etc. makes no difference.
>>
>> Help?
>>
>> John
>>
>>
>>
>> --
>> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
>> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-un
>> subscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://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? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] Problem with the sumif function

[Johnny Rosenberg]

2018-03-19 21:52 GMT+01:00 John R. Sowden :

> I had this working, then I noticed that it is no longer working (no error
> messages).
>
> Sums any cells in col F if there is a 2211 2212 or 2213 in col M:
>
> =SUMIF(M6:M351,"2211|2212|2213",F6:F351)
>

Are you sure this is supposed to work? I can't find any examples like this.

However, this works:
=SUMIF(M6:M351,2211,F6:F351) + SUMIF(M6:M351,2212,F6:F351) +
SUMIF(M6:M351,2213,F6:F351)

This also works:
{=SUM(SUMIFS(F6:F351;M6:M351;{2211;2212;2213}))}
(it's a matrix formula, so enter everything except the surrounding {}, then
hit Ctrl+Shift+Enter rather than only Enter)



Kind regards

Johnny Rosenberg


> it now gives a result of zero.
>
> I tried changing the format of the searched col to text or general number,
> no change
>
> I have set the justification to 'centered'.  Since the title is greater
> that the width of the 4 characters,
>
> there is a space before and after the 4 characters, but I think this
> should be for display only, name changing the data.
>
> Turning off centered justification, etc. makes no difference.
>
> Help?
>
> John
>
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-un
> subscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://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? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] Problem with the sumif function

[Brian Barker]

At 13:52 19/03/2018 -0700, John R. Sowden wrote:
I had this working, then I noticed that it is no longer working (no 
error messages). Sums any cells in col F if there is a 2211 2212 or 
2213 in col M:

=SUMIF(M6:M351,"2211|2212|2213",F6:F351)
it now gives a result of zero.


That use of the "|" character to mean "or" means that your criterion 
is specified by means of a regular expression. For this to work, you 
need to have this facility enabled at Tools | Options... | 
LibreOffice | Calc | Calculate | Enable regular expressions in 
formulas. Without this, the function will match only the text 
interpreted as a single fourteen-character string.


I'm guessing, but do you have this option disabled?

I trust this helps.

Brian Barker


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

Re: [libreoffice-users] need some help with custom number format code

[Brian Barker]

At 20:12 19/03/2018 +0100, Albert Oszkó wrote:

Thank you very much for this short tutorial!


No probs!

These codes are for numbering documents. The 
first part - in this case AB or AB - refer to 
the type of the course these documents belong 
to. 01 etc are serial numbers from 01 to -say- 
60. the last part /2018 refers to the current 
year and should not be incremented.


Aha: it's always best to define your problem accurately!

In that case, surely your original format 
suggestion of "AB-"##"/2018" works, with just the 
numbers 1, 2, and so on as the cell contents. If 
- as you indicated - you want single digit 
numbers to have a preceding zero, change this to "AB-"00"/2018" .


Brian Barker  



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

[libreoffice-users] Problem with the sumif function

[John R. Sowden]

I had this working, then I noticed that it is no longer working (no 
error messages).


Sums any cells in col F if there is a 2211 2212 or 2213 in col M:

=SUMIF(M6:M351,"2211|2212|2213",F6:F351)

it now gives a result of zero.

I tried changing the format of the searched col to text or general 
number, no change


I have set the justification to 'centered'.  Since the title is greater 
that the width of the 4 characters,


there is a space before and after the 4 characters, but I think this 
should be for display only, name changing the data.


Turning off centered justification, etc. makes no difference.

Help?

John



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

Re: [libreoffice-users] need some help with custom number format code

[Albert Oszkó]

2018-03-19 19:33 keltezéssel, Brian Barker írta:

At 15:06 19/03/2018 +0100, Oszkó Albert wrote:
I would like to create a custom number format code in Calc, to spare 
some work. This would be like this AB-01/2018. I would like to 
increment only the 01 part by dragging that little rectangle on the 
lower right of the cell. So I created as custom formats 
"AB-"##"/2018" or "AB-"\/. Unfortunately in either case only the 
year number is incremented and not what I wanted.


I'm not sure that's true - but it depends anyway on the actual values 
you propose to enter into the cells. Are you suggesting entering the 
number 1 and filling down to produce 2, 3, and so on - or to enter the 
date 1 January 2018 (in whatever format) and filling that down to 1 
February 2018 and so on? In addition, is the 2018 part fixed, or do 
you want that eventually to change? Do you want 12/2018 to be followed 
by 13/2018, that is, or by 01/2019?


Your first form works if the cells contain just the numbers 1, 2, and 
so on - not dates - though it would have to use "00" in place of "##" 
if January were to appear as "01" and not as "1" and so on. And it 
runs out if you want 2019 to appear when appropriate.



How to proceed?


You could easily assemble this using a formula by concatenating the 
text with a formatted version of the relevant date. For example, put 1 
January 2018 (as a date) in A1. If you fill down from this, you will 
get 2 January and so on, which is not what you want. Instead enter 1 
February 2018 in A2. Now select A1 and A2 together and drag the fill 
handle: that will give you 1 March, 1 April, and so on. Now in B1 enter

="AB-"(A1;"MM/")
and fill that down the column. You can hide the unwanted column A or 
put it away on another sheet if you prefer.


You could avoid the extra column by generating the date in the formula:
="AB-"(DATE(2018;ROW();1);"MM/")
Fill that down to achieve the desired effect. If your values start in 
a row other than the first, you will need to change "ROW()" to 
"ROW()-x", where "x" is some appropriate value. Note that this formula 
relies on the ability of the DATE() function to interpret the 
thirteenth month of 2018 as January 2019 and so on.


But yes: you can achieve this by formatting dates. Just use the format 
"AB-"MM/. Once again, you will need to enter your first two dates 
(1 January and 1 February 2018) into the first two cells, select both, 
and then drag the fill handle so as to create dates one month apart, 
not one day apart, as would happen if you try to fill from a single 
prototype.


I trust this helps.

Brian Barker

Thank you very much for this short tutorial! These codes are for 
numbering documents. The first  part - in this case AB or AB- refer to 
the type of the course these documents belong to. 01 etc are serial 
numbers from 01 to -say- 60. the last part /2018 refers to the current 
year and should not be incremented.


Regards, Albert


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

Re: [libreoffice-users] need some help with custom number format code

[Brian Barker]

At 15:06 19/03/2018 +0100, Oszkó Albert wrote:
I would like to create a custom number format 
code in Calc, to spare some work. This would be 
like this AB-01/2018. I would like to increment 
only the 01 part by dragging that little 
rectangle on the lower right of the cell. So I 
created as custom formats "AB-"##"/2018" or 
"AB-"\/. Unfortunately in either case only 
the year number is incremented and not what I wanted.


I'm not sure that's true - but it depends anyway 
on the actual values you propose to enter into 
the cells. Are you suggesting entering the number 
1 and filling down to produce 2, 3, and so on - 
or to enter the date 1 January 2018 (in whatever 
format) and filling that down to 1 February 2018 
and so on? In addition, is the 2018 part fixed, 
or do you want that eventually to change? Do you 
want 12/2018 to be followed by 13/2018, that is, or by 01/2019?


Your first form works if the cells contain just 
the numbers 1, 2, and so on - not dates - though 
it would have to use "00" in place of "##" if 
January were to appear as "01" and not as "1" and 
so on. And it runs out if you want 2019 to appear when appropriate.



How to proceed?


You could easily assemble this using a formula by 
concatenating the text with a formatted version 
of the relevant date. For example, put 1 January 
2018 (as a date) in A1. If you fill down from 
this, you will get 2 January and so on, which is 
not what you want. Instead enter 1 February 2018 
in A2. Now select A1 and A2 together and drag the 
fill handle: that will give you 1 March, 1 April, and so on. Now in B1 enter

="AB-"(A1;"MM/")
and fill that down the column. You can hide the 
unwanted column A or put it away on another sheet if you prefer.


You could avoid the extra column by generating the date in the formula:
="AB-"(DATE(2018;ROW();1);"MM/")
Fill that down to achieve the desired effect. If 
your values start in a row other than the first, 
you will need to change "ROW()" to "ROW()-x", 
where "x" is some appropriate value. Note that 
this formula relies on the ability of the DATE() 
function to interpret the thirteenth month of 2018 as January 2019 and so on.


But yes: you can achieve this by formatting 
dates. Just use the format "AB-"MM/. Once 
again, you will need to enter your first two 
dates (1 January and 1 February 2018) into the 
first two cells, select both, and then drag the 
fill handle so as to create dates one month 
apart, not one day apart, as would happen if you 
try to fill from a single prototype.


I trust this helps.

Brian Barker  



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

[libreoffice-users] need some help with custom number format code

[Oszkó Albert]

Hi all,

I would like to create a custom number format code in Calc, to spare 
some work. This would be like this AB-01/2018. I would like to increment 
only the 01 part by dragging that little rectangle on the lower right of 
the cell. So I created as custom formats "AB-"##"/2018" or "AB-"\/. 
Unfortunately in either case only the year number is incremented and not 
what I wanted. How to proceed? I use LO 5.4.1.2.0 on openSuse Tumbleweed 
with KDE.


Thanks, Albert



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