# Excel Complicated Sort

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!

• Why not try a pivot table? – Raystafarian Apr 20 '17 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 '17 at 18:07

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.

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

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

• 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 '17 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 '17 at 20:39
• Remove Duplicates from column S then apply the formula – yass Apr 20 '17 at 20:46
• 0 means PRCP >0 – yass Apr 20 '17 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 '17 at 4:20

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

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