Help figuring out chart design

[Will Finkle]

Hi,

I'm working on a template to analyze restuarant menus. I am building a chart
(Stacked Bar) showing the prices of the items along the X axis, with the
column heights being the relative popularity of the items sold at that price.
I need the stacks because some prices can obviously be the same for different
menu items, and I need to show the popularity not just of the price, but of
each menu item as well.

I uploaded a screenshot of the chart as it stands now if you want to take a
look:
http://www.mediafire.com/imageview.php?quickkey=9pummux56g1&thumb=4

My source data looks like this:
A-total B C D E
2% 2%
24% 3% 10% 9% 2%
15% 10% 5%
21% 5% 9% 2% 5%
7% 7%
7% 2% 5%
23% 10% 5% 8%

Column A is the total percentage, and is the overall height of each bar. The
BCDE values are each menu item's sales popularity. (it is a dynamic range
which can expand both down & to the right depending on the # of menu items
and the # of different prices)

I have gotten so far as to display the % of each menu item in their
respective stacks, but showing the name of the items would be much more
useful. Can I assign a staggered row range, identical in shape to the data
range, to be the data labels?

Another problem is that I can't seem to separate each column so that the
data range in my spreadsheet's Columns B, C, D, and E, are not formatted all
the same -- there is no sequence or pattern betweeen menu prices, and the way
Excel colors all the stacks at the same "level" the same inhibits
understanding the meaning of the chart.

I'd like to color the stacks randomly, or maybe use two alternating colors
from one stacked bar to another, perhaps alternating a pattern between the
individual stacks in a bar.

Is a Stacked Bar the wrong choice? Should I combine a stacked bar with
something else? I've considered embedding the chart into a worksheet with the
menu item names being displayed beneath the chart in approximately the same
"shape" as the stacked bar. I've even considered trying to display the menu
item names in cells and trying to figure out how to size the cell heights
according to their percentages, but could be some serious "common
denominator" crunching... not cool at all!

Any suggestions?

Thank you very much!

 

[Joel]

I looked at you webpage and have the following comments

1) The A-B C D should be highlighted when you make the chart. The Letters
will appear on the x-axis.

2) Series names can be included in a seperate column to the left of the
data. These can be highlighted also when you make the chart and will appear
in the legend.

3) You should look at the various option in the chart option. Right click

 

[Will Finkle]

I have uploaded an improved version of the chart -- I found XT Chart Labeler
on AppsPro, so now I am able to display the menu item names in each segement.
The chart is good now, but ideally, the bars would be colored according to
column, not series. Here is the link:

http://www.mediafire.com/imageview.php?quickkey=10wdxjy2ddz&thumb=4

And below is a better model of the data:

Price Total Item Ratios
Point Ratio
--------------------------------------------------------------------
$7.95 2% 2%
$8.95 25% 3% 10% 9% 2%
$9.50 15% 10% 5%
$10.95 21% 5% 9% 2% 5%
$11.95 7% 7%
$12.95 8% 2% 5%
$13.95 22% 10% 5% 8%

Price Point is the X-axis label, and actually each row of Price Point data
is the series to be charted, but I can't seem to get this to work. i.e. for X
value $8.95, there are 4 segements: (3%, 10%, 9%, 2%). So I am forced to use
the column data as the series instead, but there is no logical grouping of
them on an axis.

Also, I realized maybe I should have posted this in the Charting groups, so
I'm going to post there as well.

Thanks 1,000,000!