Re: [libreoffice-users] COUNTIF / DAYS / ?

On 07/05/2017 23:48, Remy Gauthier wrote:
> Hi,
>
> If you have one row per day of the year, you can find out easily where 
> the row corresponding to "TODAY()-1" is located by finding out the 
> number of days between Jan 1st of that year and "yesterday". This would 
> be something like (you may need to adjust based on the heading of your 
> column(s)):
>
> =DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1)
>
> The formula above calculates the number of days between yesterday last 
> year and Jan 1st of last year. Now, you need to find in which column to 
> add. Since you seem to have a heading providing the year, you can use 
> the MATCH() function to locate the column:
>
> =MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)
>
> This should give you the column where last year's data is located (I 
> used a1 to z1, but any row reference will work).
>
> To add the values, you now just need to use a combination of SUM() and 
> OFFSET():
>
> =SUM(OFFSET($A$2;0;MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-1;DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1);1))
>
> What that does:
>
> OFFSET() allows you to create dynamic arrays for functions like SUM(). I 
> used the anchor point $A$2 (I supposed your heading is in row 1), and 
> told OFFSET to move from that reference 0 rows down and 
> "MATCH(YEAR(TODAY()-1)-1;$a$1:$z$1;0)-1" columns to the right; then, 
> from that new point, make an array that is 
> "DAYS(YEAR(TODAY()-1)-1;MONTH(TODAY()-1);DAY(TODAY()-1);YEAR(TODAY()-1)-1;1;1)" 
> rows high and 1 column wide. The resulting array is then used by SUM() 
> to calculate the total rainfall.
>
> This will probably need to be adjusted a bit to take into account the 
> position of your column heading and to correctly find the row where to 
> stop, but you should now have something to play with.
>
> I hope this helps.
>
> Rémy Gauthier.

Thank you for the in-depth reply! I will need to play with this  and the 
functions you mention to get it right but hopefully I will come right. I 
will post my final formula here, for anyone else wanting to do something 
similar.


Regards
Hylton
> Le dimanche 07 mai 2017 à 21:55 +0200, Hylton Conacher (ZR1HPC) a écrit :
> > Hi,
> >
> > I have a spreadsheet I use to draw graphs and extract information about
> > the rainfall in my area for the last two years.
> >
> > I need to create a formula that will count how many days it rained
> > between the start of the year, both last year and this year, so I can
> > compare the amount of rainfall that was received during that time frame.
> >
> > The example below is a sample dataset that shows how much rainfall was
> > received in 2016 or 2017, on each of the four days
> >
> > Example:
> > Month Day       2016    2017
> > Jan      1      0       15
> > Feb      29     1       0       
> > May      6      0       1
> > Sept     22     15      5
> >
> >   From this we can determine that 16mm of rain fell in 2016 over 2 days.
> > Likewise we can determine that 21mm of rain fell in 2017 but over 3 days.
> >
> > Today is the 7th of May 2017 and I would like a formula to work out how
> > many days the rainfall received between(and incl) 2016/01/01 and the day
> > before TODAY last year. The answer for 2016 is 1 i.e. it only rained
> > once between the dates specified, however the answer for 2017 is 16 as
> > it rained twice between TODAY-1 and 2017/1/1. I had thought of using
> > COUNTIF or DAYS or =COUNTIF(C2:SUM(TODAY()-1,">0")), however the problem
> > is that I do not have a single date column, but three.
> >
> > This formula will allow me to create a graph showing how much rainfall
> > had fallen last year compared to this year between the beginning of the
> > year(01/01) and the day prior to Today.
> >
> > I had thought of converting the first two columns into a single and
> > having the text name of the month with each successive line being a new
> > date in that month until the month changed, however I was unable to get
> > it to work i.e. automatically change month after 31 days in January to
> > read February 01.
> > As you can imagine I have 367 rows of data per year, mostly with 0 as a
> > value, however there are odd days it does rain.
> >
> > Any comments, and if you want the original spreadsheet, just yell where
> > to put it i.e. Nabble etc.
> >
> > Regards
> > Hylton


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted