By Jim Gordon, co-author of
Office 2011 for Mac All-in-One For Dummies
in collaboration with
SUNY at Buffalo Professor Robert Wetherhold, AMSE fellow
These instructions explain how to make an Average-Max-Min chart like this one:
These instructions are for Microsoft Excel 2011.
Instructions for Excel 2013 can be found here.
Notice that our example research data is in the range B2:E10. We will not attempt to directly graph the research data. Instead we will calculate the maximum, average, and minimum for each column of the dataset and make our chart from the calculations. A range below the dataset has been started for this purpose. The heading information was copied and pasted from the research data-set. When working on your own data, be sure to leave at least two completely blank rows between the end of your dataset and the calculated data's heading information.3. Enter the following formulas to calculate the maximum value for each column in the data-set:
In our example, our values must be divided by 1,000,000 to be converted from Pa to MPa, so our formulas include a division by 1,000,000. When you work with your own data you would not include division or correction factors unless you need to change units.
When choosing the cell range with your own data, use the column that covers the largest complete data range and then copy that formula to the adjacent columns. MAX, MIN and AVERAGE worksheet functions ignore blanks, but zero values are included.4. Enter the following formulas to calculate the average value for each column in the data-set:
6. Select the cell range B14:E16 to select the calculations
In our example we chose zero decimal places, like this:
You're now ready to make your chart.
Your calculated data and chart should look like this:
23c. In the Arrows group, change End size to Arrow R, size 1, then click the OK button to close the Format High-Low Lines dialog.
Your chart looks excellent right now, but there are some finishing touches that can make it look truly professional. Next, format the chart's legend so that only Average displays.24. Click once on the Legend to select it. The corners of Legend should be highlighted, as shown:
25. Click into the Legend to select Max. The Legend highlight turns off, and Max becomes highlighted, as shown:
26. While Max is selected, on your keyboard, press the Delete key to delete the Max label from the Legend.
You should save your work at this point.
Chart template files (.crtx) can be shared with other Excel Mac and PC users. Put chart template files into this (usually hidden Library) directory on your Mac:To save your chart as a picture:
UserName/Library/Application Support/Microsoft/Office/Chart Templates/
Here's a suggested location for PC users:
C:\Users\UserName\AppData\Roaming\Microsoft\Templates\Charts Save your chart as a Picture file
Add Standard Deviation to your chart