Overlaying standard on scatter diagram

1st thought: it is going to be difficult to create an XY scatter plot with this data, because you do not have any "numbers" to use for the X axis data. I think this may be easier as a line chart rather than XY scatter.

2nd thought: If I understand what you are describing, this sounds like a variation on a "box and whisker" diagram (without the whiskers). See this tutorial: http://peltiertech.com/excel-box-and...ams-box-plots/ You don't need all of the statistical analysis to get the quartiles, but what I want you to pay particular attention to is how he arranges the data in the spreadsheet. I find that a lot of charting questions are really about how to arrange the data in the spreadsheet. With the data arranged correctly in the spreadsheet, the chart is easy to create.

Note, in particular, how he arranges the data by category (alpha, beta, etc.), then the desired chart values below each category. In order to match up with the tutorial, you might arrange your data in the same way -- with sales brackets across the top of the table, and the desired plot data in rows below. I would envision something like:

Please Login or Register  to view this content.

The quickest and easiest way to get this kind of arrangement might be a simple IF() function. With your sales brackets in J1:P1, the formula might look something like =IF($C3=J$1,$D3,na()) in J3. Note how I am using a mix of relative and absolute references to make this easy to copy. Then copy this formula into J3:P100 (or however large you need the range to be). Then follow his steps to create the "combination stacked column + line" chart.

Let us know if and/or where you get stuck.