PART 1 – CREATING AN HISTORICAL WEATHER DASHBOARD
It’s hard to think of an activity that depends on the weather and climate more than gardening. How cold does my location get? When is the last frost date? Is it too hot for this kind of plant? Do I need to water, or will it rain tomorrow? Every plant and every activity that takes place in the garden is dependent upon the weather. So, we thought it would be interesting to use Microsoft Excel to better understand how we can answer some common garden weather questions. In this three-part blog series, we are going to use Microsoft Excel to answer some of the weather and climate questions that gardeners ask.
- Part 1 – Creating Historical Weather Dashboard
- Part 2 – Creating a Microsoft Excel based Weather Forecast
- Part 3 – Create a Growing Degree Days Calculator using Historical and Forecast Weather Data
Each of these blogs discuss where to find weather data and techniques to process the data.
Part 1 – Creating an Historical Weather Dashboard
In part one of the blog we will create a Historical Weather Dashboard which provides the information that a gardener needs to understand the weather and climate they will experience in a particular location. This will assist the gardener in determining what plants will grow well in their location, help them in the timing of the planting and plan for ongoing care throughout the seasons.
The dashboard has three sections:
- Summary of key gardening indicators
- Temperature detail
- Precipitation detail
A key information section at the top that shows a summary of the temperature and precipitation experienced at the location. This includes:
First and last frost dates and growing season length – these tell the gardener when certain plants can be grown and how many days per year are available to grow plants.
Hottest and coldest temperatures experienced – these help the gardener identify plants that will grow well in the historical temperature range.
The calculated USDA Hardiness Zone – based on the average low temperature, this is a standard measure of plant cold weather hardiness issued by the United States USDA.
The weekly rainfall and the average annual rainfall – these indicate to the gardener whether certain plants will grow well without help or if irrigation will be required.
Importing the historical weather data
To start the dashboard, we need to import historical weather data for the locations we are interested in. To do this, we are using the Visual Crossing Weather add-in. This add-in makes it easy for us to import historical weather records and weather forecast data into Excel, so we can perform the data analysis.
In our raw data sheet, we copy the location address from the first page of our Workbook. We also fill in the dates that we are interested in collecting the historical weather data for. The Visual Crossing Weather add-in then uses the location address and the date information to populate a table that includes the weather data we required for our dashboard (and a lot more information that we won’t use in this example). For more information on the Visual Crossing Weather add-in, please see the tutorial.
Now we have data, we are ready to begin analyzing the data and creating our dashboard.
Temperature records
We will first create an analysis of the minimum and maximum temperatures experienced at the location. Let’s first outline some key garden-related questions we need to answer.
What is the coldest my location gets?
To help us decide what plants will survive the winter, we can look at the simple value of “What is the coldest temperature we can expect?” Using a simple calculation within excel we can find the minimum temperature for each year:
To calculate these results, we look at the minimum and maximum temperatures for each year using the formula:
=MINIFS(MinTemp,DateTime,">=" & DATE($B10,1,1),DateTime,"<=" & DATE($B10,12,31))=MAXIFS(MaxTemp,DateTime,">=" & DATE($B12,1,1),DateTime,"<=" & DATE($B12,12,31))
These two formulae find the MIN and MAX IF the dates of the row are in the correct year (between the 1st January and 31st December).
To create our summary, we then show the maximum and minimum values across all years analyzed. Finally, we look up the USDA hardiness zone based off a look up table in ‘Zone Lookup’ sheet.
What is the USDA Hardiness Zone? The United States USDA produces the “Plant Hardiness Zone Map” that gives us an indication of the expected minimum temperature. Many plant suppliers and shops will indicate the hardiness zone on the plants to help buyers identify plants that will grow in a location. By knowing our zone and confirming it here in our Excel Workbook, we now know what kind of plants to buy. For more information, see planthardiness.ars.usda.gov/
The USDA plant hardiness map suggests a normal minimum temperature is between -5 to 0F (-20.6-17.8C) for our location in Herndon, VA. This represents zone 6b. Does our 10-year temperature record also show that? Yes – we have a minimum of -2.11F which puts us in the middle of zone 6b.
So now we know what plants should survive in our location. Let’s now find out when we should start planting.
When should I plant my seeds and frost sensitive plants?
It’s Spring where we are writing this in the East Coast of the USA and one of the most important questions facing gardeners now is “when the right time is to plant my plants?”
If we plant too early then a late frost might kill the young plants – too late and some plants will suffer in the hot, dry summer (plus we gardeners are excited to get our plants in the ground!) We can analyze our historical weather records to identify when the last frost dates have occurred in the Spring and when the first Frost dates occur in the Fall.
For our Herndon location, we can see that the last frost date in normally in the middle of April and the first Frost date in the Fall (Autumn) ranges between middle of October until the middle of November.
To calculate these first and last frost dates, we need to perform a slightly more complex calculation. We are looking for the last value where temperatures of 32F or below occur.
=MAX(IF(ISNUMBER(DateTime)*ISNUMBER(MinTemp),IF((YEAR(DateTime)=$B10)*(DateTime-DATE(YEAR(DateTime),7,1)<0)*(MinTemp<32),DateTime)))
In this calculation, after checking that we have valid dates, temperatures and the correct year, we calculate the difference between all dates and the middle of summer (July 1st). This value will be negative for values in Spring and positive for fall. For example, June 30th will be value -1 because it is day before July 1st.
The last frost date is day is the date where the date difference between that date and July 1st is least negative (i.e. closest to July 1st) and the temperature is less than 32F. The first frost day is the minimum positive date difference where temperatures of 32F occur.
How many days growing season are there between the last and first frost date?
The final calculation we perform is to calculate the number of days between the last and first frost dates. We find this by simply finding the difference of the two dates.
We can now create a growing season summary using the median of the frost dates and growing season length. Care should be taken with using these values because there is a 50% chance that a frost will occur after this date! Maybe it would be better to use the last ever measured Frost date (April 18th) and check the weather forecast, just in case?
Precipitation records
Precipitation (rain, snow etc.) is just is vital for plant wellbeing as temperature. However, the gardener has a little more ability to control the amount of precipitation that a plant may receive. To help plan the garden for rainfall, we look at the historical precipitation records.
Here we see the amounts of precipitation for each month for the past 10 years. As gardeners we look at the average amount of rainfall in a month. The extreme values can be useful for planning and mitigation for extreme rainfall events or for identifying any risk of drought. In this example you can see that 2016 was a particularly dry year and 2018 was an extremely wet year.
We can also show the monthly averages on a graph to give us an easy way to view the data and compare months:
Finally, we can summarize this information into our executive summary section by indicating the expected rainfall per week during the growing season the mean annual rainfall:
Next Part – Creating a Microsoft Excel based Weather Forecast
We now have our completed Historical Gardening Weather dashboard. We can use this investigate how weather has affected out location over the past ten years. How cold? How Hot? How much rain? We are now armed with the information to identify what plants will grow well in our environment. We are also armed with the information on when to plant to avoid frost damage (or to help seeds that need frost to germinate).
In our next blog, we will look forward in time to the weather forecast so we can be prepared in the garden.
If you would like to download the completed Microsoft Excel workbook, you may download it here: Gardening_Weather_Workbook.xlsx