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_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
>
>
>
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: [email protected]
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
