|
|
Peltier Tech
Books at Amazon.com
Buy me a coffee If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee. |
Area Combination Chart for Monthly Temperatures.
In Column Combination Chart for Monthly Temperatures, I showed how to create a stacked column chart which summarized the past month's temperatures. It's an informative chart, and compares each day's minimum and maximum temperature with the normal range and the historical minimum and maximum temperatures for each date. Jack Jordan sent along a similar graphic based on area chart series, and asked if I could reproduce it in Excel.
That isn't actually the scanned chart he sent me, it's my reproduction in Excel of the chart he sent, in all of its colorful glory. If I apply the colors of my original stacked column chart, it looks pretty good:
The chart looks complicated, but it can be broken down into smaller components:
*Floating/Stacked Area Charts are very similar in data layout and chart construction to Floating/Stacked Column Charts. The DataThe important daily temperature data is shown below. I wish I could tell you where to download it from, but I don't know; instead I manually digitized the chart from the newspaper.
In order to generate floating and stacked area series, we need to make adjustments to the data. The new data for the Record High/Low and Normal Range series must include a hidden series on which the others float (using the Record Low value), a series containing the difference between Normal Low and Record Low, a series containing the Normal Range (the difference between Normal High and Normal Low), and a series containing the difference between Record High and Normal High. The new data for the Daily Range needs a hidden series (the Daily Low) and a series showing the Daily Range (the difference between Daily High and Daily Low). The circles around the month's High and Low require two more series that only have plottable data for the actual high and low data points. The transformed data is shown here:
The formulas required to construct this range are summarized below. The formulas in H5:N5 are filled down to H34:N34.
The formulas in M4 and N4 concatenate the label and value; these will become the series names, and the series will be labeled using the Series Name option. The formulas in columns M and N below these labels assure that only the maximum and minimum will appear in the chart; NA() produces an ugly #N/A error in the sheet, but a Line or XY chart ignores this when plotting points. Finally, we need special data for the series which will be used for the labels along the right edge of the chart. The range is shown below, along with the formulas which define these values.
The lookup formulas in column Q are more intricate than necessary (as are those in column P), but they allow rapid scaling up if the data were converted to a dynamic range. They determine the latest value of each of the relevant series, for proper vertical alignment of the labels. Note that these formulas refer to a range ending in row 34, but for a 31 day month these should be changed to row 35. Even better would be using dynamic names for these ranges, or converting the worksheet range to a "List" if using Excel 2003. The uncorrected label positions in Q8 and Q14 in the original version of this page may cause the "Normal Range" and "Daily Range" labels to overlap. The complicated formulas in Q7 and Q13 and the factor in Q20 allow for moving of these labels if they would overlap on the chart. Making the ChartThe first step is to make a stacked area chart from the historical data. Select A4:A34 (Date), then hold Ctrl while selecting H4:K34 (Bottom, Below, Normal, and Above). Start the chart wizard, and create a Stacked Area chart. (Don't make a Stacked 100% Area chart.)
The chart has my usual plot area formatting: black border, white fill, and light gray gridlines. Make the Bottom series invisible: double click on the series, and on the Patterns tab of the Format Series dialog, choose None for Area and for Border. Double click on the Below series and choose light gray for Area and None for Border. Format the Above series the same, by selecting it and pressing the F4 function key (the shortcut for Repeat Last Action). Format the Normal series with a medium gray for Area and None for Border. Other adjustments at this point included deleting the legend and formatting the X axis major spacing to 7 days. This is starting to look like a chart.
Note: for my light gray color, I used a custom color in the Excel color palette. I went to the Tools menu > Options > Color tab, clicked on the "dark yellow" square (the greenish-brown square, third from the left in the second row), clicked on Modify, and selected the third lightest gray on the custom color tab. I used the lightest gray on Excel's palette (just above the white square) as my medium gray color. Now the Daily High and Low data is added to the chart. Select F4:F34 (Daily Low), hold Ctrl while selecting L4:L34 (Range), and copy (Ctrl+C or Copy from the Edit menu). Select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, and Series Names in First Row.
Move these two added series to the secondary axis, so they will appear in front of (not stacked on top of) the series created earlier. Double click one of the series, and on the Axis tab, select Secondary. Select the other series, and press the F4 function key (the shortcut for Repeat Last Action).
You don't need the Y axes on both sides of the chart, especially since different default scales will make maintenance of the chart more tedious. Remove the secondary Y axis: select Chart Options from the Chart menu, click on the Axes tab, and uncheck the Secondary Y Axis box. Now all series use the primary axis for their scaling, but they retain their separation between primary and secondary axis groups.
Now format these series to look like a single floating area. Make the Daily Low series invisible: double click on the series, and on the Patterns tab of the Format Series dialog, choose None for Area and for Border. Double click on the Range series and choose an appropriate color (I used orange to match the chart in the newspaper) for Area and None for Border.
Add the series which will indicate the month's minimum and maximum temperatures. Copy M4:N34 (Monthly Low and Monthly High), select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, and Series Names in First Row.
These were added as more stacked area series. Select one of these series, and using Chart Type on the Chart menu, change its chart type to Line. Select the other series and press the F4 function key. Double click one series; on the Patterns tab of the Format Series dialog, select None for Line and a large (size 7) circle with black (for example) as its Foreground and No Color for its Background; on the Data Labels tab, check the Series Name option. Select the other line series and press the F4 function key. Double click on the Monthly High data label, and on the Alignment tab, choose Above for Label Position. Double click on the Monthly Low data label, and on the Alignment tab, choose Below for Label Position.
Add the series that will locate the descriptive labels. Copy P4:Q7 (Record, highlighted in blue), select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, Series Names in First Row, and Categories (X Labels) in First Column. Copy P11:Q12 (Daily Range, highlighted in green), select the chart, and choose Paste Special from the Edit menu. Choose New Series, Values in Columns, Series Names in First Row, and Categories (X Labels) in First Column. Since the last series was converted to a line series, these series are added as line series; Record is shown in blue and Daily Range in green.
To align these series along the right edge of the chart, they need to be converted to XY series. Select one of these series, and using Chart Type on the Chart menu, change its chart type to XY (Scatter). Select the other series and press the F4 function key.
Add labels to the series, then make the points invisible. Select the Daily Range series, and on the Data Labels tab, select the Series Name option; on the Patterns tab, select None for Line and Markers. For the Record series, there are three choices:
I've used orange and gray font colors so the labels are more clearly associated with the historical series in the chart.
Temperatures Above and Below ZeroMy chart shows temperatures in Boston using the Fahrenheit scale, where temperatures below zero are very rare. Jack's data set had a twist that I hadn't encountered in mine: his temperatures were for a location in Canada using degrees Celcius, so he had positive and negative temperature values. The nice thing about using stacked area series is that no modifications are needed to depict negative temperatures in this chart.
The only modification that might be necessary is to move the X axis. If you like the axis to serve as a marker for 0°, then no change in needed. If you want the X axis at the bottom of the chart, double click the Y axis. On the Scale tab, enter an appropriate value in the Category (X) Axis Crosses At box. You could either enter the exact value where you want the axis to cross, or to be sure it will always cross at the bottom, enter a value that is less than you ever expect to need; I used -100. Since this value is less than the axis minimum, Excel just uses the axis minimum.
There are two versions of this chart which use columns rather than areas to display the temperature data. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Peltier Technical Services, Inc.Commercial Utilities | Custom Solutions | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2010. All rights reserved. |
