Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?
Hi,
If I understand your question correctly, you need to be able to
"automatically" index in your data matrix, for total sum and number of
days. To achieve this, I would change the worksheet in the following
manner:
Formula in D2: =SUM(OFFSET(D3;0;0;MATCH(TODAY()-1;$C$3:$C$368;0);1))
This formula could then be copied (dragged) to E3-> I3. This will work
for all the years in your spreadsheet because the date you have in
column C are "2018". This will break in 2019. You could set the dates
in column C to something like =DATE(YEAR(TODAY()),,) to get around the issue.
The value in AF373 should be set to 2017 instead of 2017 Rain Days to
Date; its format, however, would need to be changed to #" Rain Days to
Date" (with the quotes). This allows having a numerical field with text
in it that can still be used in formulas. Then, the formulas in AF374
to AF385 become:
AF374:
=IFERROR(COUNTIF(OFFSET($E$3,0,0,IF(DATE(AF$373,MONTH(TODAY()),DAY(TODA
Y()))-
1>DATE(AF$373,1,DAYSINMONTH(DATE(AF$373,1,1))),DAYSINMONTH(DATE(AF$373,
1,1)),DATE(AF$373,MONTH(TODAY()),DAY(TODAY()))-
DATE(AF$373,1,1)),1),">0"),0)
AF375:=IFERROR(COUNTIF(OFFSET($E$34,0,0,IF(DATE(AF$373,MONTH(TODAY()),D
AY(TODAY()))-
1>DATE(AF$373,2,DAYSINMONTH(DATE(AF$373,2,1))),DAYSINMONTH(DATE($AF373,
2,1)),DATE(AF$373,MONTH(TODAY()),DAY(TODAY()))-
DATE(AF$373,2,1)),1),">0"),0)
AF376 and others: the month in the DATE functions changes to the month
in question (3, 4, 5, etc.); the IFERROR sets the answer to zero when
there is an error (the OFFSET function gets a negative height in the
months after the current date). Currently, I am using
OFFSET(something,0) but you could also set it to OFFSET(something,"")
to be able to distinguish between an actual zero rainfall (0) and an
error (null string).
The same treatment would then be applied to the data in column AH.
I hope this helps.
Rémy Gauthier.
Le samedi 28 avril 2018 à 11:38 +0200, Luuk a écrit :
> On 28-4-2018 09:14, Luuk wrote:
> > On 27-4-2018 18:04, Hylton Conacher (ZR1HPC) wrote:
> > > Hi,
> > >
> > > I am running LibreOffice 5.4.6.2 and am using a spreadsheet to
> > > enable
> > > me to draw graphs based on rainfall received per year, up until
> > > the
> > > date prior to today. The figures also allow me to compare current
> > > rainfall to date to the same period in a previous year. I agree I
> > > should probably use Base but I do not think it would have solved
> > > the
> > > issue.
> > >
> > > **Example Data***,
> > > Month,Day,M/D,2016,2017,2018,Average('16->'17)
> > > Apr,1.04/01,3,0,1,1.5
> > > Apr,2,04/02,0,8,0,4
> > > ,,
> > > ,,
> > > ,,
> > > Apr,26,04/26,0,0,20,0
> > >
> > > Assuming today is Apr 2nd, a formula to count how many days of
> > > rain
> > > there were in the current month would be =COUNTIF($F$2:$F2,">0").
> > > The
> > > Total amount of rain that fell on that number of days, being 1,
> > > was
> > > 1mm, or taking it further 60% of the average rainfall to date for
> > > the
> > > month.
> > >
> > > Assuming today is Apr 27, a formula to count how many days of
> > > rain
> > > there were in the current month is unknown, especially as there
> > > are
> > > undoubtedly values in the other un-shown 81 cells i.e. 3yrs and
> > > 27
> > > remaining days?
> > >
> > > Basically take the starting cell reference $F$2 and add the
> > > number of
> > > events that were ">0" to get the end reference for the COUNTIF
> > > formula.
> > >
> > > =COUNTIF($F$2:Add number of items ">0" to the starting line
> > > reference
> > > 2, to get the end of the COUNTIF range) i.e. being Apr 27, whilst
> > > there may be 26 days between the beginning of Apr and today i.e.
> > > Today()-1, there are only two instances in the above table where
> > > ">0"
> > > for a particular year, thus making 2 the number of days it
> > > rained, to
> > > receive the SUM of the 2018 rainfall being 21mm
> > >
> > > I have Googled silly but my GFu is lacking something as the
> > > solution
> > > evades me, even after reading the LibreOffice help files.
> > >
> > > For reference the formula to count the number of days from 1st
> > > Apr to
> > > Yesterday:
> > > =DAYS(TODAY(),"2018-04-01")
> > >
> > > I have uploaded the file to
> > > > > w/view?usp=sharing>
> > > for folk to view. quite willing to upload the spreadsheet
> > >
> > > I will need to re-write at least 36 formulae being one for each
> > > month
> > > of three years, but then hopefully I can leave column AC and
> > > concentrate on entering the data correctly and optimising the
> > > graphs.
> > >
> > > Ideally I think I need a formula to give me the number of days
> > > between
> > > $F$2 and 'TODAY() -1' and add that number to the cell reference
> > > to
> > > create the end of the COUNTIF range.
> > >
> > > I hope you can point me in a direction that will help.
> > >
> > > Regards
> > > Hylton
> > >
> >
> > This can be solved using the INDIRECT function
> >
> >
[libreoffice-users]
Enviado con AquaMail para Android http://www.aqua-mail.com -- 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] Ubuntu 18.04 installed LibreOffice version
On 04/27/2018 11:45 PM, Tim-L wrote: I just installed Ubuntu-Mate 18.04LTS I looked and they installed LibreOffice 6.0.3.2. Though there are issues with Ubuntu's 18.04 OS, it is nice to see an up to date LO at an OS install time. Well, I just upgraded - not clean install - and it kept the 5.4.4.2 version. I just downloaded the newest version for install. -- 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] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?
On 28-4-2018 09:14, Luuk wrote:
> On 27-4-2018 18:04, Hylton Conacher (ZR1HPC) wrote:
>> Hi,
>>
>> I am running LibreOffice 5.4.6.2 and am using a spreadsheet to enable
>> me to draw graphs based on rainfall received per year, up until the
>> date prior to today. The figures also allow me to compare current
>> rainfall to date to the same period in a previous year. I agree I
>> should probably use Base but I do not think it would have solved the
>> issue.
>>
>> **Example Data***,
>> Month,Day,M/D,2016,2017,2018,Average('16->'17)
>> Apr,1.04/01,3,0,1,1.5
>> Apr,2,04/02,0,8,0,4
>> ,,
>> ,,
>> ,,
>> Apr,26,04/26,0,0,20,0
>>
>> Assuming today is Apr 2nd, a formula to count how many days of rain
>> there were in the current month would be =COUNTIF($F$2:$F2,">0"). The
>> Total amount of rain that fell on that number of days, being 1, was
>> 1mm, or taking it further 60% of the average rainfall to date for the
>> month.
>>
>> Assuming today is Apr 27, a formula to count how many days of rain
>> there were in the current month is unknown, especially as there are
>> undoubtedly values in the other un-shown 81 cells i.e. 3yrs and 27
>> remaining days?
>>
>> Basically take the starting cell reference $F$2 and add the number of
>> events that were ">0" to get the end reference for the COUNTIF formula.
>>
>> =COUNTIF($F$2:Add number of items ">0" to the starting line reference
>> 2, to get the end of the COUNTIF range) i.e. being Apr 27, whilst
>> there may be 26 days between the beginning of Apr and today i.e.
>> Today()-1, there are only two instances in the above table where ">0"
>> for a particular year, thus making 2 the number of days it rained, to
>> receive the SUM of the 2018 rainfall being 21mm
>>
>> I have Googled silly but my GFu is lacking something as the solution
>> evades me, even after reading the LibreOffice help files.
>>
>> For reference the formula to count the number of days from 1st Apr to
>> Yesterday:
>> =DAYS(TODAY(),"2018-04-01")
>>
>> I have uploaded the file to
>>
>> for folk to view. quite willing to upload the spreadsheet
>>
>> I will need to re-write at least 36 formulae being one for each month
>> of three years, but then hopefully I can leave column AC and
>> concentrate on entering the data correctly and optimising the graphs.
>>
>> Ideally I think I need a formula to give me the number of days between
>> $F$2 and 'TODAY() -1' and add that number to the cell reference to
>> create the end of the COUNTIF range.
>>
>> I hope you can point me in a direction that will help.
>>
>> Regards
>> Hylton
>>
> This can be solved using the INDIRECT function
>
> Example
> A1: 1
> B1: 2
> C1: 3
> D1: "A1:C1"
> E1: =AVERAGE(INDIRECT(D1))
>
> This will result in E1 showing the average value of cells A1:C1
>
>
>
or, another example
if you change B374 to: 01-01-2016
and B375 to: EOMONTH(B374;0)+1
and copy B375 to B376:B385, and change Format cell to 'MMM'
C374: ="D3:D"&(2+A374)
C375:
="D"(3+EOMONTH(B374;0)+1;"01-01-2016")&":D"(3+EOMONTH(B375;0)+1;"01-01-2016")
copy C375 to C376:C385
change D374 from '=SUM(D$3:D$33)' to '=SUM(INDIRECT(B374))'
copy D374 to D375:D385
Then values in your sheet should look like this (range A374:D386):
31 jan *D3:D33*1
28 feb D34:D63 0
31 mrt D63:D94 59
30 apr D94:D12444
31 mei D124:D155 19
30 jun D155:D185 137
31 jul D185:D216 151
31 aug D216:D247 83
30 sep D247:D277 51
31 okt D277:D308 15
30 nov D308:D338 3
31 dec D338:D369 44
365 *Total*
607
--
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] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?
Morning Brian,
Thanks for the reply.
On 2018/04/27 20:20, Brian Barker wrote:
At 18:04 27/04/2018 +0200, Hylton Conacher wrote:
I am running LibreOffice 5.4.6.2 and am using a spreadsheet to enable
me to draw graphs based on rainfall received per year, up until the
date prior to today. The figures also allow me to compare current
rainfall to date to the same period in a previous year. I agree I
should probably use Base but I do not think it would have solved the
issue.
**Example Data***
,
Month,Day,M/D,2016,2017,2018,Average('16->'17)
Apr,1.04/01,3,0,1,1.5
Apr,2,04/02,0,8,0,4
,,
,,
,,
Apr,26,04/26,0,0,20,0
Assuming today is Apr 2nd, a formula to count how many days of rain
there were in the current month would be =COUNTIF($F$2:$F2,">0").
Surely not? Rather something like =COUNTIF($F$94:$F94,">0").
Correctly extrapolated to the real data set as opposed to the example
data set. Sorry, I just checked the example data set and I seemed to
have mucked it up.
The Total amount of rain that fell on that number of days, being 1,
was 1mm, ...
If we are talking 2018, that seems to be zero in your data.
You are correct and have extrapolated the formula to the REAL data set
correctly.
... or taking it further 60% of the average rainfall to date for the
month.
I don't know what fraction zero is of zero - as I'm not allowed to
divide by zero. Does your mathematics allow this?
:) Apologies. 0 of 0 is still 0. I was using the example data so folk
did not have to download the spreadsheet.
Assuming today is Apr 27, a formula to count how many days of rain
there were in the current month is unknown, ...
Surely not? The current month (I think it's still 2018, unless some
catastrophe has happened without my knowing) has apparently had six
rainy days in your data.
Again you are correct, just using a different data set.
... especially as there are undoubtedly values in the other un-shown
81 cells i.e. 3yrs and 27 remaining days?
Where are these "unshown" cells - somewhere else, and not in this
spreadsheet at all? And how can the "current month" - surely April 2018
- contain over three years' worth of days?
Disregard as I was referring to my example data as instead if writing
values for 26/27 days across three years. You are looking at the REAL
data set and there are NO missing values.
Basically take the starting cell reference $F$2 ...
Perhaps $F$3?
Again wrong data set. The F3 was based on the example data not REAL data.
Take the starting cell reference as you correctly identified as $F94...
... and add the number of events that were ">0" to get the end
reference for the COUNTIF formula.
Now you've lost me ...
If my starting reference is F94 SUM onto 94 the number of rows in column
F those rows that are ">0", but only add them if the date is between the
date of F94 i.e. 2018-04-01 and Yesterday's date.
=COUNTIF($F$2:Add number of items ">0" to the starting line reference
2, to get the end of the COUNTIF range) i.e. being Apr 27, whilst
there may be 26 days between the beginning of Apr and today i.e.
Today()-1, there are only two instances in the above table where ">0"
for a particular year, ...
Are you working with the current month - one month? Or with all the
Aprils in three years - three non-contiguous months? Or with the period
from 1 January 2016 up to yesterday? Or what?
I am working in a month for a year i.e. April in 2018
... thus making 2 the number of days it rained, ...
That's still six in your spreadsheet.
Correct
... to receive the SUM of the 2018 rainfall being 21mm
Er, 67 mm?
Correct
Incidentally, you have the value 119 in L2 to indicate yesterday's row
number, but this has evidently been added manually and also must be
updated so. It should be fairly easy to establish this automatically,
and even to embed any such calculation in any formula.
Now this sounds promising. Can I reference a cell with a manually
entered line number i.e. L2 on the REAL data has a value of 120 and use
that value to complete a formula i.e. COUNTIF($F$94:F(Value in L2),
">0") i.e. COUNTIF($F$94:F120, ">0")
Sorry for the confusion. Next time I raise a query I am only going to
refer to my REAL data.
Regards
Hylton
--
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] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?
On 27-4-2018 18:04, Hylton Conacher (ZR1HPC) wrote:
> Hi,
>
> I am running LibreOffice 5.4.6.2 and am using a spreadsheet to enable
> me to draw graphs based on rainfall received per year, up until the
> date prior to today. The figures also allow me to compare current
> rainfall to date to the same period in a previous year. I agree I
> should probably use Base but I do not think it would have solved the
> issue.
>
> **Example Data***,
> Month,Day,M/D,2016,2017,2018,Average('16->'17)
> Apr,1.04/01,3,0,1,1.5
> Apr,2,04/02,0,8,0,4
> ,,
> ,,
> ,,
> Apr,26,04/26,0,0,20,0
>
> Assuming today is Apr 2nd, a formula to count how many days of rain
> there were in the current month would be =COUNTIF($F$2:$F2,">0"). The
> Total amount of rain that fell on that number of days, being 1, was
> 1mm, or taking it further 60% of the average rainfall to date for the
> month.
>
> Assuming today is Apr 27, a formula to count how many days of rain
> there were in the current month is unknown, especially as there are
> undoubtedly values in the other un-shown 81 cells i.e. 3yrs and 27
> remaining days?
>
> Basically take the starting cell reference $F$2 and add the number of
> events that were ">0" to get the end reference for the COUNTIF formula.
>
> =COUNTIF($F$2:Add number of items ">0" to the starting line reference
> 2, to get the end of the COUNTIF range) i.e. being Apr 27, whilst
> there may be 26 days between the beginning of Apr and today i.e.
> Today()-1, there are only two instances in the above table where ">0"
> for a particular year, thus making 2 the number of days it rained, to
> receive the SUM of the 2018 rainfall being 21mm
>
> I have Googled silly but my GFu is lacking something as the solution
> evades me, even after reading the LibreOffice help files.
>
> For reference the formula to count the number of days from 1st Apr to
> Yesterday:
> =DAYS(TODAY(),"2018-04-01")
>
> I have uploaded the file to
>
> for folk to view. quite willing to upload the spreadsheet
>
> I will need to re-write at least 36 formulae being one for each month
> of three years, but then hopefully I can leave column AC and
> concentrate on entering the data correctly and optimising the graphs.
>
> Ideally I think I need a formula to give me the number of days between
> $F$2 and 'TODAY() -1' and add that number to the cell reference to
> create the end of the COUNTIF range.
>
> I hope you can point me in a direction that will help.
>
> Regards
> Hylton
>
This can be solved using the INDIRECT function
Example
A1: 1
B1: 2
C1: 3
D1: "A1:C1"
E1: =AVERAGE(INDIRECT(D1))
This will result in E1 showing the average value of cells A1:C1
--
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
