1

I am trying to sort several thousand rows of weather data. Each row represents a date (col. C, DATE) and the only other column I am concerned with is the precipitation level (col. D, PRCP.)

My goal is to find the date within the calendar year that is most consistently not rainy.

The date is in the 8 character format (i.e. 19800111 = Jan. 11, 1980) and I would need to count the calendar date while ignoring the first four digits (year) if the PRCP column (D) is 0. This (I am assuming) would keep a tally for that date's number of dry days. At the end of the function, the day with the highest count would be the historically driest day of the year.

Does that make sense? Is there a better way to approach? How would this be scripted?

Attached a screenshot of the csv columns. I am in Excel for Mac v 15.25 Thanks for any insight!

CSV Screenshot

Improve this question
2
  • 2
    Why not try a pivot table?
    – Raystafarian
    Apr 20, 2017 at 19:53
  • A pivot table is EXACTLY what you want. Weather data is commonly used to teach pivot tables to boot
    – Selkie
    May 8, 2017 at 18:07

3 Answers 3

Reset to default
1

Truncate the first four digits of the date (For example, RIGHT(A1, 4)), then sort by the truncated date. Then just do a SUM() after each unique date.

Improve this answer
2
  • Any fast way to do this without manually doing SUM 365 times?
    – pixelkicker
    Apr 20, 2017 at 20:18
  • Check this link for creating Subtotal.
    – Vylix
    Apr 21, 2017 at 2:04
1

Write the right 4 digits (month and day) in a new column
In S1 write =RIGHT(C1,4) and drag it down select column S and Remove Duplicates 

Column S      Column T
    0101      =Sumproduct(--Right($C$1:$C$9999,4)=S1)*(--$D$1:$D$9999))
    0102
    0103

=SUMPRODUCT(--(RIGHT($C$1:$C$9999,4)=S1)*(--$D$1:$D$9999=0))

where C1:C9999 is the Date column
D1:D9999 is the PRCP column
Change it to correspond your Data and keep $
You can drag the formula to have the count for each day
Then sort the 365 days descending
Result 0in T means PRCP >0
The Formula count only when PRCP is 0

Improve this answer
6
  • Don't quite follow ... I've made the S column as indicated but when I place and drag what you have for T col. I just get 0 on all fields.
    – pixelkicker
    Apr 20, 2017 at 20:37
  • how did you write the column S is it showing 0101 and the Date in C is text or Date?
    – yass
    Apr 20, 2017 at 20:39
  • Remove Duplicates from column S then apply the formula
    – yass
    Apr 20, 2017 at 20:46
  • 0 means PRCP >0
    – yass
    Apr 20, 2017 at 20:51
  • i think that is the issue. if it has EVER rained on that date it gets a zero. I need to just find the driest day and not auto zero a date just for raining once or twice.
    – pixelkicker
    Apr 21, 2017 at 4:20
0

Assuming that range B7:B9999 stores the dates and precipitation values are in C7:C9999 (example with some random PRCP data)

enter image description here

enter image description here

, fill D7:D9999 with the day of the year (DOY) number using formula 

=DATE(MID(B7,1,4),MID(B7,5,2),MID(B7,7,2))-DATE(MID(B7,1,4),1,1)+1

Then type in F7 an array formula (to enter the formula, press Ctrl+Shift+Enter instead of just Enter for scalar formula) 

=MIN(SUMIF($D$7:$D$9999,ROW($A$1:$A$365),$C$7:$C$9999)/COUNTIF($D$7:$D$9999,ROW($A$1:$A$365)))

, which will show the minimal average of day precipitation. Another array formula 

=MATCH(F7,SUMIF($D$7:$D$9999,ROW($A$1:$A$365),$C$7:$C$9999)/COUNTIF($D$7:$D$9999,ROW($A$1:$A$365)),0)

in G7 will show the corresponding DOY and formula

=TEXT(DATE(2001,1,1)+G7-1,"d-MMM")

in H7 will show DOY in d-MMM format.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .