Day of Year (DOY) Trend Chart

This tutorial shows you how to calculate DOY and how to create a dummy X Axis series to represent a year.  It is based on examples found in charts_doy_trend_comparison.xls

1. Multi Year Trend Chart

This trend chart shows daily flow data for a 2 year period. There is a clear seasonal affect, with summer flows considerably higher than non - summer flows.

How can we directly compare flows for the same period (say  June 2002 and June 2003)?

 2. Day of Year (DOY) Trend Chart

The DOY trend chart (above - right) presents the same data, however, we can now see the 2002 and 2003 series side by side. Notice that the peak flows in 2002 occurred in August while the peak flows in 2003 occurred in June.

There are situations where DOY trends charts can help you better understand your data.

The good news is that DOY trends charts are very doable in Excel, they just take a few extra steps to create a dummy axis series. Once you master  dummy axis series, you can use them to improve your logarithmic, probability or other chart types.

3. Organizing Our Source Data

The data table  to the right shows how we organize our trend data to create the DOY chart. We originally had the date and flow data. We then added a column for the DOY and columns for each year. To calculate the DOY, we use a date formula:  

             DOY = A2-Date(Year(A2),Month(1),0)

To assign flow data to the correct year, we use a conventional If formula:

                = If(Year(A2)=$D$1,B2,NA())

When we copy these formulas through our data set, they place flows in the correct year column and #N/A for other years.

We now have our data organized to produce our basic DOY chart.

This chart shows flows for both years by day of year. While the numeric day of year is accurate, it would be more helpful to label the days by the traditional calendar.

With a dummy axis series we can do just that.

4. Dummy Axis Series

Dummy series are a powerful tool to significantly enhance Excel charting capability. There are several sites that provide excellent "how to" information on dummy series and chart axis formatting.

1. Jon Peltier
2. Tushar-Mehta
3. Andy Pope

The basic concept is to replace Excel's standard Axis (X or Y) with a line that has data point markers to establish the desired scale and data labels connected to the markers. Just about any type of scale can be assigned to an axis using this dummy series axis method.

5. DOY Series Data Example

In this dummy axis series example, our goal is to establish a new Day of Year (DOY) data series that we can use to represent a year along an X axis of 365 days. 

We can construct our DOY series in a data table like that shown to the right. This table includes dates for the midpoint of each month, along with the calculated day of year. The Y value is the position where the X Axis will meet the Y axis. Most times that will be 0, however, in this example our Y axis range is from 10 to  20 so our DOY series Y value is 10. The last column includes the abbreviation for the month that we want to display along our X Axis.

6. Adding the DOY Series to Chart

We next add our DOY series to our chart. See link for details on how to add a new series to a chart. Once we add our new DOY series, we see that it lies directly on top of our X axis with data markers for the mid point of each month. This is exactly what we want, we want to substitute our DOY line for the X Axis and then add formatting to make our DOY line look like a typical axis.

We need to remove the X Axis formatting, then reformat our DOY series line format and add marker data labels. Finally, we need to remove the legend reference to Series 3.

We now have our chart with a DOY series and tick marks for mid point of each  month. It's beginning to look close to what we want, we simply need to add data labels to month tick marks.

7. Adding Data Labels to our Dummy Series

There are several free add-ins that provide data labels. I use John Walkenbach's Chart Tools Add-in for data labeling and a range of other routine chart manipulations.

J Walk's add-in is available on his site   http://j-walk.com/ss/excel/files/charttools.htm.

Once we add the data labels and adjust their alignment, we have our DOY trend chart. And we have learned the basics of dummy series.