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

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