Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF?

Remy Gauthier <remygauthier -AT- yahoo.com>
Sat, 28 Apr 2018 21:17:29 -0400

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()),<value for
month>,<value for day>) 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
<https://drive.google.com/file/d/1tnwBOvl2QTYeS4wkMQlg63J9oTK_UxO
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

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"&DAYS(3+EOMONTH(B374;0)+1;"01-01-
2016")&":D"&DAYS(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:D124        44
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+unsubscribe@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

Context

[libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF? · Hylton Conacher (ZR1HPC)
- Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF? · Brian Barker
  - Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF? · Hylton Conacher (ZR1HPC)
- Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF? · Luuk
  - Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF? · Luuk
    - Re: [libreoffice-users] Calc: Calculate number of days until yesterday IE TODAY-1 to assist in array determination for COUNTIF? · Remy Gauthier

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.