|A standard multi year trend chart shows data continuously over
the trend period. If a User wants to compare year to year seasonal
patterns, then a Day of Year (DOY) trend chart may be in order
because it shows individual series for each year over 365 days. This
lets a User compare conditions on specific days or periods from one
year to the next.
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
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
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
DOY = A2-Date(Year(A2),Month(1),0)
To assign flow data to the correct year, we use a conventional If formula:
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
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.
- 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
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
J Walk's add-in is available on his site
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.