Date: prev next · Thread: first prev next last
2017 Archives by date, by thread · List index


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.


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

Context


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.