An Impossible Chart...

[ShockleySC]

OK, so I am trying to use stacked bar charts to display the following set of data...and it isnt working out. Pls help!!

There are three mountains...mt1 mt2 and mt3
Each mountain has some number of green apple trees and a red apple trees
Each tree can produce some number of large and some number of small apples

So, I want to see a bar chart that has mt1, mt 2 and mt3 across the bottom. Over each mt label I want to see two bars going up, one goes up to a height that represents the total number of green apple trees on that mountain. The other does the same for the red apple trees on that mountain. Now I want each of those two bars split at some point to graphically show how many apples were bi vs small for that tree.

And then repeat that for each mountain. And oh by the way, I need it yesterday!!! I am in Excel 2007. PLS HELP ME COUNT MY APPLES!!! Hahaha acutally its not apples at all, its missle parts but you get the idea.

 

[VoG]

Here are some places to look

http://www.andypope.info/charts.htm

http://peltiertech.com/Excel/Charts/ChartIndex.html

http://www.tushar-mehta.com/excel/charts/

 

[jasmith4]

OK, this is tricky but it can be done. This will be a PivotChart based on a data table of the trees, which should look something like this (ignore all underscores):

 MOUNT TREE_ COLOR SIZE_ APPLES
Mount1 TreeA Green Small 41
Mount1 TreeA Green Large 50
Mount1 TreeB _Red_ Small 74
Mount1 TreeB _Red_ Large 64
Mount1 TreeC Green Small 29
Mount1 TreeC Green Large 25
Mount1 TreeD _Red_ Small 59
Mount1 TreeD _Red_ Large 72
Mount1 TreeE Green Small 61
Mount1 TreeE Green Large 71
Mount1 TreeF _Red_ Small 72
Mount1 TreeF _Red_ Large 38
Mount1 TreeG Green Small 67
Mount1 TreeG Green Large 28
Mount2 TreeH _Red_ Small 30
Mount2 TreeH _Red_ Large 54
Mount2 TreeI Green Small 46
Mount2 TreeI Green Large 38
Mount2 TreeJ _Red_ Small 58
Mount2 TreeJ _Red_ Large 29
Mount2 TreeK Green Small 35
Mount2 TreeK Green Large 50
Mount2 TreeL _Red_ Small 34
Mount2 TreeL _Red_ Large 57
Mount2 TreeM Green Small 71
Mount2 TreeM Green Large 74
Mount3 TreeN _Red_ Small 68
Mount3 TreeN _Red_ Large 61
Mount3 TreeO Green Small 57
Mount3 TreeO Green Large 51
Mount3 TreeP _Red_ Small 35
Mount3 TreeP _Red_ Large 33
Mount3 TreeQ Green Small 27
Mount3 TreeQ Green Large 58
Mount3 TreeR _Red_ Small 28
Mount3 TreeR _Red_ Large 38
Mount3 TreeS Green Small 55
Mount3 TreeS Green Large 42
Mount3 TreeT _Red_ Small 38
Mount3 TreeT _Red_ Large 44

That is, each tree has a name and a color, it's on one of the three mountains, and it produces both small and large apples. The top two rows tell you that Tree A, which is green, is on mountain 1 and produces 41 small apples and 50 large ones. You have to have two rows per tree, and they'll differ *only* in the Size and Apples columns.

Once you've got that, select the five columns, go to the Insert ribbon, drop down the triangle under PivotTable, and choose PivotChart. The dialog box should be OK already: $A:$E and New Worksheet.

Now you have a new sheet with Chart1. On the upper right you've got your five column headers under "Choose fields to add..." and four areas under "Drag fields between areas...".

Check all except Tree, and the headers will appear in the Axis Fields area. From there, drag Size to Legend Fields, and drag Apples to Values, where it will change to say "Count of Apples". Mountain and Color stay in Axis Fields, and nothing belongs in Report Filter.

Next click Count Of Apples in Values and choose Value Field Settings. In that dialog box Count is highlighted, but what you really want is Sum, so double-click that. ("Count" really means the number of data rows, not apples).

You should now have a chart with three pairs of bars, blues next to reds. To stack them, right-click the chart, choose Change Chart Type, and choose the second or fifth Column chart. This should be what you want.

Finally, you'll see three gray drop-down buttons on the chart: drop down each of them and uncheck "(blank)". You can also right-click the edge of the chart, choose Move Chart and then choose New Sheet, so you have the pivot table and pivot chart both on separate sheets.

 

[ShockleySC]

Thank You. Thank YOU! Thanks, you. To YOU be thanks! Thanks in abundance be upon you. Many thanks! Thank thank you you! And of course, thanks!

OK seriously, thanks for the inputs! I have it solved now. Honestly I deeply appreciate it. And, .... thanks.