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

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_UxOw/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

--
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