Pareto Chart from Pivot Table

[hitmanwa]

I'm trying to create a Pareto Table from a Pivot table. The table is set up but I'm trying to link the data using a dynamic ranged name. Is there a way to get rid of the #N/A values or am I completely complicating this process all together?

Pareto Chart

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 63px"><COL style="WIDTH: 35px"><COL style="WIDTH: 69px"><COL style="WIDTH: 48px"><COL style="WIDTH: 44px"><COL style="WIDTH: 55px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Start Date</TD><TD style="TEXT-ALIGN: center">Blend</TD><TD style="TEXT-ALIGN: center">Total</TD><TD style="TEXT-ALIGN: center">Loss </TD><TD style="TEXT-ALIGN: center">% Loss</TD><TD style="TEXT-ALIGN: center">Cum Loss</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: left">Oct</TD><TD style="TEXT-ALIGN: left">60</TD><TD style="TEXT-ALIGN: left"> 71,104 </TD><TD style="TEXT-ALIGN: left"> 17,800 </TD><TD style="TEXT-ALIGN: left">25.03%</TD><TD style="TEXT-ALIGN: left">0.24%</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD style="TEXT-ALIGN: left">100</TD><TD style="TEXT-ALIGN: left"> 65,096 </TD><TD style="TEXT-ALIGN: left"> 9,672 </TD><TD style="TEXT-ALIGN: left">14.86%</TD><TD style="TEXT-ALIGN: left">0.37%</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD style="TEXT-ALIGN: left">101</TD><TD style="TEXT-ALIGN: left"> 71,568 </TD><TD style="TEXT-ALIGN: left"> 9,792 </TD><TD style="TEXT-ALIGN: left">13.68%</TD><TD style="TEXT-ALIGN: left">0.50%</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD style="TEXT-ALIGN: left">102</TD><TD style="TEXT-ALIGN: left"> 35,372 </TD><TD style="TEXT-ALIGN: left"> 2,864 </TD><TD style="TEXT-ALIGN: left">8.10%</TD><TD style="TEXT-ALIGN: left">0.54%</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD style="TEXT-ALIGN: left">103</TD><TD style="TEXT-ALIGN: left"> 260,924 </TD><TD style="TEXT-ALIGN: left"> 17,396 </TD><TD style="TEXT-ALIGN: left">6.67%</TD><TD style="TEXT-ALIGN: left">0.77%</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>{=IF(ISERROR(PivotBlend),"",PivotBlend)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[hitmanwa]

Any ideas?

Thanks again,
Hit

 

[Caleab]

Sorry that I don't have ideas for you on this one.
but I did want to comment....

I've not heard anyone else refer to a Pareto chart in a few years. Was kinda nice to see =)

 

[Greg Truby]

You're not really giving us a lot to go on here. A pareto is naught but a sorted column chart. All you would need to do is click on the field settings for your row field and have it sort by the appropriate data field.

Or, if you are trying to build the chart source data in a separate range use the LARGE() function, probably in conjuction with the ROW() function.

 

[hitmanwa]

Sorry Greg,

I'll try to explain it as best I can. I have the pivot table set to sort descending by %Loss. I'm not sure if it's possible to add another field that provides the cumulative % but I need to look. For now, what I've done is use the array formula to populate by the blend and then vlookup functions to pull the data I need from the table. Finally, I'm just using sum formulas to calculate the cumulative loss. The problem I'm having is once I run out of blends on the pivot table I get N/A returns thus making the Pareto Chart look bad as those values are included in the chart. I can post a SS if that would help. I'm using excel 2000 for this one btw.

Thanks for the patience and help,
Hit