Pie Chart Defects

Hi,

I would like to create a Pie Chart that demonstrates the various defects, I have vs. the # of good parts produced.

For this example, I have 4 types of products or charts that I would like to compare on a monthly basis.

Perhaps, later, I would like to see these products all on one worksheet, instead of 12 different worksheets, but at the moment, I'll have a worksheet for each month.

Right now, I have a drop-down box that will specify the month's metrics.

How can I make thee pie charts work? I attached an Excel 2003 file for example.

Thanks

You can use formula to extract the chart data using INDEX.
The drop down result is not linked to a cell. This value can be used as the colum argument in the INDEX function.

Consider using alternative chart type as comparison using pie will be almost impossible.

hi mycon73,

Originally Posted by Andy Pope

Consider using alternative chart type as comparison using pie will be almost impossible.

If you want some elaboration on Andy's comment regarding the usefulness of pie charts then reading the below link may influence your perspective... http://www.perceptualedge.com/articl...or_dessert.pdf

hth
Rob

Hi Andy,

Thank you very much for the Pie Chart example. I'm also using Pareto charts but wanted to if I would get the same results or something radically different. In addition, I think if the Pie Chart is giving accurate results, then it would give a bigger or better picture of where the defects are.

I don't quite understand your index formula.

Cell D7 appears to be an empty cell
I don't see how Cell B20 is associated to any other cell or formula. I do see that there's a Cell Link to B20 if I edit the drop down.

Also, how is this associated with the drop down?

Now, that I'm evaluating the formula a little bit more, I do see a formula association in the, "-ROW($D$7),$B$20)" portion of the formula.

Hi Broro183,

Thanks for the literature.... I'll read up on this literature.

The drop down control is linked to B20.
Right click dropdown and pick Format control to see the Cell Link.

The INDEX formula,

=INDEX($H$8:$S$14,ROW()-ROW($D$7),$B$20)

Uses B20 to determine the Column of data from H8:S14
The ROW()-ROW($D$7) determines the row to use.

I could have used this where the range to index is the columns on the current row.

=INDEX($H8:$S14,1,$B$20)

Here is some reading material on dynamic ranges.
http://peltiertech.com/Excel/Charts/Dynamics.html

Hi Andy,

Thanks for addition clarification. Why is the formula referencing Cell D7, which appears to be blank? Is this or can be used if add an additional dynamic, such as specifying a specific product line?

Thanks again...

As I said. The ROW()-ROW($D$7) determines the row number reference for the INDEX formula when the reference table has multiple rows.

ROW($D$7) returns the value 7.

So when used in the first row of the table ROW() returns 8 and the result of 8-7 is 1.
For the second row of the table ROW() = 9 and 9-7 is 2.

So on and so on for all the rows in the table.

Hi Andy,

Thanks for the explanation - I did not initially see this relation or functionality. Now, I know how to use this Pie Chart better.