A commonly used chart type for statistical analysis is a Main Effects Plot. I won’t go into the statistics behind this chart type here, but I want to show how easy it can be to construct such a chart. For this example, suppose there are three main effects, designated X1, X2, and X3, and an experimental matrix is designed such that each is set to either a high value (+) or a low value (-). The means of an output variable is calculated for the high and low input values, and these are placed on a chart.
To make the chart in Excel, set up your values as shown below. Cells C1 through E1 contain the names of the three main effects, and F1 contains “Mean”. A2, A4, and A6 also hold the main effects’ names, while B2 through B7 contain alternatively plus and minus signs. A1, B1, A3, A5, and A7 are blank. The mean for tests with factor X1 at its high level is in cell C2, and at its low level is in cell C3; the corresponding values for X2 and X3 are in D4:D5 and E6:E7. The overall mean is filled in F2:F7.
| A | B | C | D | E | F | |
| 1 | X1 | X2 | X3 | Mean | ||
| 2 | X1 | + | 60 | 71.5 | ||
| 3 | – | 83 | 71.5 | |||
| 4 | X2 | + | 72 | 71.5 | ||
| 5 | – | 71 | 71.5 | |||
| 6 | X3 | + | 74 | 71.5 | ||
| 7 | – | 69 | 71.5 |
Select this whole range, A1:F7, run the chart wizard and create a line chart (Excel 97-2003) or go to the Insert tab and choose a line chart (Excel 2007). The blanks in A1:B1 tell Excel to use columns A and B for the two-row category axis labels, and row 1 as series names. The blanks in A3, A5, and A7 center the X1, X2, and X3 category labels under each pair of +/- category labels. Very little formatting is needed. I left the X1, X2, and X3 series with their default line and marker formats, and changed the Mean series to a dashed line with no markers.
This is another example of what Excel can do if you look beyond the defaults and beyond the standard chart types. It also validates one of my favorite expressions: if you spend five minutes with the data, you can save five hours of frustration.
Jon says
Jon,
I like this chart. It’s very clean and easy to understand. This is probably out of scope with the post, what kind of data would allow a higher negative value than a positive value (example X1)? I would like to adapt this chart to some of the analysis that I do. Thanks again.
John Mansfield
Jon Peltier says
John – Say I’m investigating corrosion rate against pH, temperature, and chrome content of my steel alloy. Low pH would corrode faster, high temperature would corrode faster, and low chrome would corrode faster. That’s one positive and two negative effects.
Jon Peltier says
Actually, the factors need not be numerical. The +/-, high/low designations are arbitrary. You could consider them to be like True/False, or like any choice among categorical factors. Red/Green, Left/Right, Male/Female, Jack/Jill. What matters isn’t the order in which you list the factors, but the slope of the line connecting the two values.
TV says
When you use two columns as the category axis, is there a way to change the formatting on that second row? If this is done in a general sense, longer words get truncated/overlapped easily…
Jon Peltier says
You can change the orientation of the category labels, but only the set of labels closest to the axis are affected. Below are charts with the labels horizontal and vertical. The vertical ones fit better, but are arguably no easier to read. The best you can do is pick short mnemonic labels.
Jaganmohan Gandhi says
Hi John,
I have a data in % for response against X. How to get the % in Y-Scale in main effect plot . could you help in excel or in minitab.
Regards
Jon Peltier says
If the Y values are formatted as percentages in the worksheet, Excel will plot them as percentages. I haven’t used Minitab in at least 15 years, so I can’t help you there.
Mindy says
Awesome post!! Super helpful…just what I needed
Ramon says
Great post! Easy to follow and very helpful.
Thanks a lot!