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


Not sure if I fully understand what you are looking at, 
but here is what I have attempted.
Assuming you column data is:
A is month
B is day of Month
C is the rain on day in 2016
D is the rain on day in 2017

I've added Column E and Column F to be real date value.
E2 =DATE(2016,MATCH(A2,$G$1:$G$12),B2)
F2 =DATE(2017,MATCH(A2,$G$1:$G$12),B2)

Copied for all lines in column A-D.
Since the month has to match exactly, I put the month
names in G1-G12 these would need to match with how 
you entered months.

In B13 put Days
In C13 forumula to get number of days with rain in range.
=COUNTIFS($C$2:$C$5,">0",$E$2:$E$5,">"&DATE(YEAR(TOD
AY())-1,MO
NTH(TODAY()),DAY(TODAY())))

First part is to not count days with 0 values,
Second part is to only get the date range with year - 1.
(You could subtract on from day, but that would cause issue on 
1st,
so would probable need to convert date to  julian format and 
subtract 1)

In D13
=COUNTIFS(D2:D5,">0",$F$2:$F$5,"<="&TODAY())

Put Rain in B13

In C13 sum total
=SUMIF($E$2:$E$5,"<="&DATE(YEAR(TODAY())-1,MONTH(TO
DAY()),DAY
(TODAY())),$C$2:$C$5)
Doesnt' matter if we sum up zero entries.

In D13
=SUMIF($F$2:$F$5,"<="&TODAY(),$D$2:$D$5)

Results I get are 1 day in 2016 with total of 1mm of Rain.
Results for 2017 is 2 days with 16mm of Rain.

My sheet is on one of my college servers.

ftp://fedora9gcc.dyndns.org/rain.ods


On 7 May 2017 at 21:55, Hylton Conacher (ZR1HPC) wrote:

Send reply to:          hylton@conacher.co.za
To:                     LibreOffice Users <users@global.libreoffice.org>
From:                   "Hylton Conacher (ZR1HPC)" 
<hylton@conacher.co.za>
Subject:                [libreoffice-users] COUNTIF / DAYS / ?
Date sent:              Sun, 7 May 2017 21:55:45 +0200

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


+----------------------------------------------------------+
  Michael D. Setzer II -  Computer Science Instructor      
  Guam Community College  Computer Center                  
  mailto:mikes@guam.net                            
  mailto:msetzerii@gmail.com
  Guam - Where America's Day Begins                        
  G4L Disk Imaging Project maintainer 
  http://sourceforge.net/projects/g4l/
+----------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS
ABC         16613838.513356 | EINSTEIN   133913302.288695
ROSETTA     60333252.687309 | SETI       104587492.242787


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