NJ Strong Weather Forum


Join the forum, it's quick and easy

NJ Strong Weather Forum
NJ Strong Weather Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

+6
Radz
RJB8525
billg315
Frank_Wx
sroc4
Math23x7
10 posters

Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Math23x7 Tue Feb 10, 2015 8:52 pm

Okay guys, this has been something I have been working on for years, but never thought about sharing until recently.  

I imported the data from NWS Upton's list of NYC snowfall per season (http://www.erh.noaa.gov/okx/climate/records/monthseasonsnowfall.html) onto Excel and computed the mean and standard deviation of the list.  
In the "Seasons" tab, The "A" column represents the number season NYC measured snow (i.e. 2013-14 was the 145th full season of snow measurements), the "B" column gives the snow season, and the "C" column gives the snow total.  The "D" column gave each season a "status" with with terms that NOAA does with their monthly and annual temperature and precipitation maps of how far from normal the seasons were. In the "E" and "F" columns, I have the Rankings in both Snowiest and Least Snowiest, respectively, on record.  The "G" column has the Cumulative Seasonal Average and the "H" column has the change in this average (note how cell H2 has a zero next to it).  The "I" column has the Departure from the 145-year average and the "J" column has the Standardized Departure from the 145-year average.  Finally, The "K" column has the departure from normal from the 1981-2010 average (25.8").  

In the "Seasons by Rank" tab, I sorted the seasons based on value.  You can play around with this one if you want.

The "Summary" Tab includes the value of the Mean and Standard Deviation of the list.  One thing to keep in mind is that I had to choose whether to use =stdev.p (population) or =stdev.s (sample).  Since this does not include the snowfall data from January-April 1869 and since it there are no official measurements before then, I chose to use the Sample Standard Deviation.  If you think it should be Population Standard Deviation, I will make the change as necessary.

This does not include the 2014-15 season as I want to wait until it's over to put it in.

I would have shown this at the get together a month ago, but never got around to it.

Hope you enjoy this!
Attachments
Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Attachment
NYC seasonal snow statistics.xlsx You don't have permission to download attachments.(53 Kb) Downloaded 79 times


Last edited by Math23x7 on Sun Apr 08, 2018 7:51 pm; edited 3 times in total

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 2379
Reputation : 68
Join date : 2013-01-08

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by sroc4 Tue Feb 10, 2015 9:01 pm

Wow Mike this is amazing. Great job!

_________________
"In weather and in life, there's no winning and losing; there's only winning and learning."
WINTER 2012/2013 TOTALS 43.65"WINTER 2017/2018 TOTALS 62.85" WINTER 2022/2023 TOTALS 4.9"      
WINTER 2013/2014 TOTALS 64.85"WINTER 2018/2019 TOTALS 14.25" WINTER 2023/2024 TOTALS 13.1"
WINTER 2014/2015 TOTALS 71.20"WINTER 2019/2020 TOTALS 6.35"
WINTER 2015/2016 TOTALS 35.00"WINTER 2020/2021 TOTALS 37.75"
WINTER 2016/2017 TOTALS 42.25"WINTER 2021/2022 TOTALS 31.65" 
sroc4
sroc4
Admin
Admin

Posts : 8326
Reputation : 301
Join date : 2013-01-07
Location : Wading River, LI

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Frank_Wx Tue Feb 10, 2015 9:09 pm

Mike I can't wait to go home and download this onto my comp. Seems amazing. Thanks for this

_________________
_______________________________________________________________________________________________________

CLICK HERE to view NJ Strong Snowstorm Classifications
Frank_Wx
Frank_Wx
Godzilla Seeker
Godzilla Seeker

Posts : 21301
Reputation : 328
Join date : 2013-01-05
Age : 31
Location : Jersey City, NJ

http://njstrongweather.blogspot.com/

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by billg315 Tue Feb 10, 2015 9:09 pm

Well done. And a little nostalgic. Looking through the chart reminded me of the snow drought I experienced during high school (I think we had one snow day in four years) when some of my classmates thought the days of snowy winters had passed us by (a bit of early global warming hysteria I think). Only to be followed by couple momentous snowy winters in college.
billg315
billg315
Advanced Forecaster - Mod
Advanced Forecaster - Mod

Posts : 4438
Reputation : 185
Join date : 2015-01-24
Age : 50
Location : Flemington, NJ

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by RJB8525 Tue Feb 10, 2015 9:23 pm

Wow that's really awesome
RJB8525
RJB8525
Senior Enthusiast
Senior Enthusiast

Posts : 1994
Reputation : 28
Join date : 2013-02-06
Age : 38
Location : Hackettstown, NJ

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Radz Tue Feb 10, 2015 9:35 pm

Really great job! Thanks for sharing!
Radz
Radz
Pro Enthusiast
Pro Enthusiast

Posts : 1027
Reputation : 17
Join date : 2013-01-12
Location : Cortlandt Manor NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by docstox12 Wed Feb 11, 2015 9:49 am

OMG, Mike, an outstanding job! We are always in need of this chart during our winter comparisons.

CP, do you see what Mike has done here? Incredible work and patience.

Thanks, Mike!!!!
docstox12
docstox12
Wx Statistician Guru
Wx Statistician Guru

Posts : 8472
Reputation : 222
Join date : 2013-01-07
Age : 73
Location : Monroe NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by CPcantmeasuresnow Wed Feb 11, 2015 9:56 am

Have not had a chance yet Doc to much travel and work lately. I'm hoping to sit and take a look this weekend.
CPcantmeasuresnow
CPcantmeasuresnow
Wx Statistician Guru
Wx Statistician Guru

Posts : 7265
Reputation : 230
Join date : 2013-01-07
Age : 103
Location : Eastern Orange County, NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Dtone Thu Feb 12, 2015 3:55 am

billg315 wrote:Well done. And a little nostalgic. Looking through the chart reminded me of the snow drought I experienced during high school (I think we had one snow day in four years) when some of my classmates thought the days of snowy winters had passed us by (a bit of early global warming hysteria I think). Only to be followed by couple momentous snowy winters in college.

True, besides 1996 I can't remember much snowfall 6+" before college days. Snow was always N&W. 1"-3" and 2" - 4" events followed by heavy rain seem to be the standard. All snow events in NYC didn't seem nearly as common as now. Blizzard of 96 was the only snow day of my life in NY.

We averaged 30+" of snow up until the 1950s that must of been nice. Seem like we're going back toward that era during the 2000s but a dud like 2011-12 and 01-02 come along to put you in check. Still the 2000s have been amazing for snow lovers overall.

Dtone
Wx Statistician Guru
Wx Statistician Guru

Posts : 1738
Reputation : 9
Join date : 2013-08-26
Location : Bronx, NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by docstox12 Thu Feb 12, 2015 4:23 am

Well told!

After going through the 70's, 80's, and 90's ( save for 93-94 and 95-96) the 2000's have been great snow years overall.Living in Fort Lee from 1956 to 1977, what you said is true. N and W of the City always had the higher totals.It was a routine statement for weather reports on the radio and tv back then...."and areas north and west of the City will have higher snow amounts."

The 60's were pretty good compared to the period from 1970-2000.There were 7 major snowstorms in that decade.
docstox12
docstox12
Wx Statistician Guru
Wx Statistician Guru

Posts : 8472
Reputation : 222
Join date : 2013-01-07
Age : 73
Location : Monroe NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by CPcantmeasuresnow Thu Feb 12, 2015 5:41 am

Mike:

Just had a chance to download and look through the spreadsheet. I have all of this data but never bothered to conditional color format it. It makes the deviations much more pronounced.

LOVE IT!!

Any chance you have the monthly temperature data for CPK? That's something I'd love to have but have never seen in an easily accessible format like a csv file, but what do you expect from an organization that can't seem to find a way to measure snow correctly.
CPcantmeasuresnow
CPcantmeasuresnow
Wx Statistician Guru
Wx Statistician Guru

Posts : 7265
Reputation : 230
Join date : 2013-01-07
Age : 103
Location : Eastern Orange County, NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Math23x7 Tue Apr 14, 2015 3:27 pm

For the last couple of months, I have considered adding additional components to the excel document including monthly snow, precipitation, and temperature data and summarizing it, but there have been issues with getting that across.  When I inputed the monthly snow totals, there were quite a number of months containing "T" (meaning trace of snow).  I originally replaced all of these with 0 to compute the averages but I want to acknowledge that it did snow in NYC during these months (especially May 1977).  And when I kept the "T"s, Excel did not count them as months.  So I did an Excel Macro which basically counts a "T" value as zero.  

But I also wanted to put in data for other locations including Boston, Philadelphia, and Washington DC and compare them month by month and year by year.  And I also wanted to add an interactive option which enables the user to input a year or month and get a summary of all the information from the time period of his or her choosing.  Here is a preview of what I have:

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Vbacpk10

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Cpkmon10

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Msgbox10

I included the updated NYC seasonal snow statistics and as no surprise, 2014-15 had "much above normal" snowfall and the cumulative seasonal average did go up:

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 2379
Reputation : 68
Join date : 2013-01-08

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Excel chart of NYC seasonal snow statistics (updated through the 2015-16 season)

Post by Math23x7 Sun Apr 10, 2016 9:12 pm

I checked the 12Z EURO operational, and the 15-day EURO ensemble members and control.  None of them have any snow for NYC at any point during the run.  Yes one of them has flurries for Sussex County in NJ at day 11, big deal!  But I think it is safe to say that we are officially done with snow in NYC until next fall.

So with that in mind, I have updated my Excel snow chart.  CPK ended up with 32.1" of snow this season, slightly above normal, making it the 54th snowiest season on record and the 94th least snowiest season on record going back to 1869.  Of course, much of the snow ~83.5% of it actually, came from the 26.8" of snow from the January 22nd-23rd blizzard.  While some are skeptical at the measurements by the Central Park conservancy, I am still taking it as is since Upton still has the total at 32.1" (whereas last year they made some corrections in late-March.)  Another thing to note is that the cumulative snowfall average is creeping upwards.  14 years ago, it was at 27.98."  Now it is at 28.79."

As far as everything I mentioned I would do in the post above I made last April, I have been quite busy with other tasks so I have not really gotten around to it.  Maybe some day, I'll get to it though.  We'll see.  Also, I deleted the file attachment from last April since I was reaching my user storage cap.

And for all the newcomers on this forum, I hope you like this. Refer to my first post at the top from February 2015 for more specific details.

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 2379
Reputation : 68
Join date : 2013-01-08

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by docstox12 Mon Apr 11, 2016 7:46 am

Great Job Mike!!!

At least you guys S and E had a normal snowfall year.Me and CP were way below normal and the guys above I-84 way way way below normal.
docstox12
docstox12
Wx Statistician Guru
Wx Statistician Guru

Posts : 8472
Reputation : 222
Join date : 2013-01-07
Age : 73
Location : Monroe NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by CPcantmeasuresnow Mon Apr 11, 2016 4:25 pm

It's hard to find good official snowfall measurements in the HV Doc. Although Poughkeepsie has been an official reporting station since 1949 it is just for temperature and precipitation not snowfall amounts. They did take measurements for many years but were so neglectful of it they stopped using them several years ago. They actually made the zookeeper look competent in comparison.

Anyway the point I was going to make is from anything I've read for the people between I 84 and Albany this is the most snowless winter they have ever experienced. In my 45 years or so of observing it's definitely in the top 5 probably top 3 for our section of Orange County.
CPcantmeasuresnow
CPcantmeasuresnow
Wx Statistician Guru
Wx Statistician Guru

Posts : 7265
Reputation : 230
Join date : 2013-01-07
Age : 103
Location : Eastern Orange County, NY

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Math23x7 Sat Apr 01, 2017 6:01 pm

Unless we have a surprise April snow event or if Upton does revise the snow totals from last January, the Cumulative seasonal snow total will stand.  CPK ended up with 30.2" on the season, tied with 1968-69 for the 58th snowiest on record and the 90th least snowiest on record.  I have put in this update as well as updated the 2015-16 snow total to 32.8" after the revision of the January 2016 blizzard from 26.8" to 27.5."

Here is the updated Excel file.  If updates are made, I will adjust accordingly:

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 2379
Reputation : 68
Join date : 2013-01-08

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by rb924119 Sat Apr 01, 2017 8:40 pm

Math23x7 wrote:For the last couple of months, I have considered adding additional components to the excel document including monthly snow, precipitation, and temperature data and summarizing it, but there have been issues with getting that across.  When I inputed the monthly snow totals, there were quite a number of months containing "T" (meaning trace of snow).  I originally replaced all of these with 0 to compute the averages but I want to acknowledge that it did snow in NYC during these months (especially May 1977).  And when I kept the "T"s, Excel did not count them as months.  So I did an Excel Macro which basically counts a "T" value as zero.  

But I also wanted to put in data for other locations including Red Sox Suck, Philadelphia, and Washington DC and compare them month by month and year by year.  And I also wanted to add an interactive option which enables the user to input a year or month and get a summary of all the information from the time period of his or her choosing.  Here is a preview of what I have:

I included the updated NYC seasonal snow statistics and as no surprise, 2014-15 had "much above normal" snowfall and the cumulative seasonal average did go up:

Mike, I haven't looked at this, and honestly never even knew you were working on this. However, I can just tell by your discussion it's awesome! That said, maybe I can help you with your "trace" problem, because I have run into similar issues with the project that I have been working on for the forum. As I am sure you know, a true "trace" is any liquid precipitation (rainfall) that measures less than 0.005". If it measures as 0.005" or greater, it counts as 0.01". For snowfall, the first measurement is 0.1", not 0.01" like it is for rainfall. What I did to correct this in my circumstance, which is only for snowfall, is to equate "T" to 0.01 via Find and Replace. That way, it populates the cell, but also gives me a way to still identify "trace" amounts while relatively accurately maintaining a running tally to the numbers. Really, you could use any value for "trace" amounts as long as it remains below the 0.005" rounding threshold for rain and 0.05" for snowfall. As long as you keep it consistent, you could just do a simple "Find and Replace" once any new data is imported to the spreadsheet, where you Find "T" and Replace with '0.01"' in that particular column. If you wanted to more easily distinguish "trace" amounts, you could always set up a new column where the cells of the new column contain formulas linked to the preceding column with your pesky "trace" amounts. Example: if("cellx"<0.05,"T",".") for "trace" snowfall (....<0.005....for rainfall). This way, only cells in the new column with immediately adjacent cells containing "trace" values would populate with "T" and the others would be left with a ".". Just some thoughts for ya. Hopefully they made sense and either helped or jogged your mind a bit to get you started on new ideas!! Let me know if you would like further clarification, and how it goes!!

rb924119
Meteorologist
Meteorologist

Posts : 6887
Reputation : 194
Join date : 2013-02-06
Age : 32
Location : Greentown, Pa

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Math23x7 Sun Apr 08, 2018 7:48 pm

While cold air makes a return the second half of April, we may be done with the snow on the coast.  So I think I'll release my updated Excel file now.  The 2017-18 seasonal snow total ended up being 40.9", the 33rd on record and "above normal".  I should add that the Rankings for Snowiest and Least Snowiest seasons now uses a formula (whereas in the past I would sort them, auto-fill the numbers, then sort back to season.  Also, I added a small section in the Summary tab which shows that the 30-year average from 1988-89 to 2017-18 is higher than the 30-year average from 1958-59 to 1987-88.

As I mentioned earlier this thread, I have been inputing data on a separate file (not included) showing the monthly updates as well as some other random snow stats.  2013-14 to 2017-18 marks the second highest five-year total on record (211.6").  The highest belongs to 1872-73 to 1876-77 (213.7")

If we do get any additional snowfall the rest of the month (or May) or there are snowfall revisions, I will update accordingly.
Attachments
Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Attachment
NYC seasonal snow statistics (updated through 4-8-18).xlsx You don't have permission to download attachments.(60 Kb) Downloaded 12 times

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 2379
Reputation : 68
Join date : 2013-01-08

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season) Empty Re: Excel chart of NYC seasonal snow statistics (updated through the 2017-18 season)

Post by Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum