I am comparing the performances of two groups. In an Excel chart, I want to have a line that plots the max and min performance of group 1, while also charting the average performance of group 2. Excel’s stock charts (ex. High-Low-Close) might sound good for this (where close would be the average of group 2), but the problem is that when the average performance of group 2 is lower than the min performance of group 1, Excel places this average at the group 1 min. It does this because it assumes that the chart represents a single group, not two. What’s a chart that can do the same job but avoid this problem?
It may not be the styliest choice, but I would go with just three lines: Max A, Max B, Avg C. It’s quick and most people can understand it.
A box plot shows all that and a lot more. Excel doesn’t support this natively (which is just further evidence as to how piss poor it is as statistical software), but if you Google around you can figure out how to fake it.
Thanks. I was thinking that a box plot would be best, but couldn’t find it in Excel, and just wanted to be sure that it could be done somehow. thanks!
How about plotting the min/max of group 1 as error bars (but no symbol for the “actual” value), and group 2 as a separate line plot?
Does it have to be made in Excel? Because lots of other Free Software will let you make nice box-and-whisker plots, which as ultrafilter said is probably what you want. I’ve used Gnumeric in the past to do this; if you’re familiar with Excel you should have no problem using it.