Visual Crossing Blog

Using Microsoft Excel to Analyze Gardening Weather and Climate

Part 3 – Create a Growing Degree Days Calculator using Historical and Forecast Weather Data

Create a Growing Degree Days Calculator in Microsoft Excel

In our previous gardening weather blog, we have learned how to use Microsoft Excel to analyze the historical and forecast weather data of a location. We can now identify the right species of plants for a that location, when to plant them and how to be ready for the coming weather. In our final part of the series, we are going to combine past and future weather, so we can create a “Growing Degree Days Calculator”.

What are Growing Degree Days?

Before we jump into creating the workbook, we should understand exactly what a Growing Degree Day is. Simply put, they are a measure of how many days of warm temperatures has occurred so far in a plant’s development. As plants grow faster the warmer it is above a minimum temperature, the total amount of warmth that a plant experiences is a good indicator of where that plant in its grow cycle. The total amount of warmth a plant experiences is a better way to estimate the maturity of a plant than simply counting the number of days since the plant seed was sown.

For example, consider one variety of sweet corn. This variety will grow providing the temperature is above 50F. The warmer the temperatures above 50F, the faster it will grow up until 85F. Therefore, if we look at the average temperature of the day, we can create a calculation that shows how much growing occurred given the minimum and maximum temperatures:

For a particular day, where the maximum temperature is 85F and minimum temperature 55, we can therefore create a growing score:

Growing score= mean temperature-50

Therefore, in our example:

Mean temperature=(85+55/2)=70F
Score=Mean Temperature-Base Temperature=70-50
Score=20

So, this day contributed 20 Growing Degree Day (GDD) to the growth of the plant.

If we consider two other days:

Day 1 – maximum temperature 40F, minimum 35F

Mean temperature=37.5F
Score=-12.5

Negative GDD scores are treated as zero. They add nothing to the cumulative GDD but don’t take anything away either.

Day 2 – maximum temperature 95F, minimum 75F.

Corn does not grow any faster when the temperature warms above 85F and therefore we fix the maximum to 85F. Therefore adjust mean temperature.

mean temperature=(85+75)/2=80F
GDD=80-50=30F

Cumulative Growing Degree Days

As the growth of a plant occurs over the growing season, the cumulative number of the Growing Degree Days since the seed was planted gives an excellent predictor of the stage of growth of the plant (assuming the plant is correctly watered etc.) In addition, the Growing Degree Days can assist in other aspect of plant care such as when it is the best time to apply pesticides.

Creating a Growing Degree Calculator in Microsoft Excel

As we have seen, Growing Degree Days can be calculated using the maximum and minimum temperatures for each day. We can therefore use the history data created in part one of the blog using the Visual Crossing Weather Add-in for Excel.

We also want to parameterize the calculation, so we can easily substitute the Base temperature and the maximum temperature. In addition, we would like to be able to specify when a plant was planted (or some other base date). We use this date to act as the first date of the calculation. Finally, we would like to specify the date we want to focus on – typically this might be the current date.

Growing Degree Day Parameters
Growing Degree Day Parameters

For each row of data, the calculation is therefore:

=((MIN(GDD_TMax,D17)+MIN(GDD_TMax,E17))/2) - GDD_TBase

Whilst this looks a bit more complicated that the example discussed earlier, the Min() function is used to validate that the actual temperature is not greater than the maximum temperature for that plant (TMax).

Raw Growing Degree Day Results
Raw Growing Degree Day Results

By applying this to all days between the planting date and the current date, we can therefore get a cumulative GDD value so far. Remember that if negative values were found they were treated as zero.

Total Growing Degree Days Over Time
Total Growing Degree Days Over Time

Adding Weather Forecast Information to identify critical growth events

The final stage of our calculator is to add the weather forecast information. This will allow us to identify if the GDD will hit any key stages within the next week. The calculation is exactly the same except we base it off the Forecast data obtained from the weather data not the Historical data.

Forecast Growing Degree Days
Forecast Growing Degree Days

We can then add a summary display to the widget to highlight where we are now and where we will be in the forecast period:

Growing Degree Days Summary
Growing Degree Days Summary

Heating and Cooling Degree Days

Another concept that is very similar to the Growing Degree Days is the Heating and Cooling Degree Days. In this case a base temperature of 65F is chosen – any day above 65 is considered a cooling degree day and is calculated in the same way as the above Growing Degree Day. Any day below 65F is a heating degree day. The only difference to our current calculation for a Heating Degree Day is the calculation is inversed – as the temperature gets lower there are more days.

Heating and Cool Degree Days can be used as a measure of how cool or warm a season has been. This can then be used to predict energy usage for heating or cooling needs.

Next steps

In this three-part series, we have created three interactive Excel Worksheets to assist the gardener in understanding past weather, the upcoming weather forecast and combining both into more complex Growing Degree Day calculations to model plant growth.

Given a reliable source of data, you can extend this analysis much further for example indicating which of the days will have good gardening weather or helping to create a more accurate watering calendar and record.

If you would like to download the completed Microsoft Excel workbook, you may download it here: Gardening_Weather_Workbook.xlsx If you would like to change the location, download a trial of the Weather add-in for Excel or use the Weather Data services to fetch the latest weather forecast and historical data.