Stacked Bar graph

[Patrick]

This is probably an easy question for most of you but I am not an avid Excel
user, or really a basic user at all.

What I have is data from a survey that I am trying to graph. I have a
question1, an average response number, and a desired response number. I need
to create a single bar graph showing bot numbers in that bar by different
colors as the "stack." Can someone help me with how the data needs to be
setup and if there is anything else I should know. I am using Excel 2007

 

[Jon Peltier]

I would think a line chart would be better, with one series each for average
response and desired response.

- Jon

 

[Del Cotter]

What I have is data from a survey that I am trying to graph. I have a
question1, an average response number, and a desired response number. I need
to create a single bar graph showing bot numbers in that bar by different
colors as the "stack." Can someone help me with how the data needs to be
setup and if there is anything else I should know.

If you do go for a bar chart instead of a line graph as Jon suggests,
are you confident that the average will always be less-- or greater--
than the desired? If so, it sounds as though you are looking for the
stack of, say, the average response and the *shortfall* between average
and desired.

That being the case, you could go for an unstacked graph, and then use
Format Series.. Options.. Overlap=100 to put the greater of the two bars
behind the lesser. Format the table like this:

Desired Average
first 1 0.5
second 0.9 0.6

and be sure to select Data Range, Series in Columns when you step
through the Chart Wizard instructions.

Or stick with your original plan of a stacked bar, and calculate the
difference:

Average difference [ ] Desired
first 0.5 =E2-B2 1
second 0.6 =E3-B3 0.9

assuming this is the range A1:E3. Select only the first three columns
when starting the wizard, and step through the instructions for a
stacked bar graph.

If either of the two values might be greater, stick with Jon's Line
Chart format, or at the very least an unstacked bar chart with less than
100% overlap. (the first example data setup works with both these types)

Also, start the Excel Help and type "charts" into the Help Topics box,
and select either "Chart Wizard" or "charts, creating".

 

[Jon Peltier]

This makes me think of another option. Use the two lines as I suggested,
then format them to use Up-Down Bars (double click one series, go to the
Options tab). You can either leave the line series as they are, or hide them
(no lines, no markers), and you can format the Up bars differently than the
Down bars (Green or Blue for Up, Red for Down).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

What I have is data from a survey that I am trying to graph. I have a
question1, an average response number, and a desired response number. I
need
to create a single bar graph showing bot numbers in that bar by different
colors as the "stack." Can someone help me with how the data needs to be
setup and if there is anything else I should know.

If you do go for a bar chart instead of a line graph as Jon suggests,
are you confident that the average will always be less-- or greater--
than the desired? If so, it sounds as though you are looking for the
stack of, say, the average response and the *shortfall* between average
and desired.

That being the case, you could go for an unstacked graph, and then use
Format Series.. Options.. Overlap=100 to put the greater of the two bars
behind the lesser. Format the table like this:

Desired Average
first 1 0.5
second 0.9 0.6

and be sure to select Data Range, Series in Columns when you step
through the Chart Wizard instructions.

Or stick with your original plan of a stacked bar, and calculate the
difference:

Average difference [ ] Desired
first 0.5 =E2-B2 1
second 0.6 =E3-B3 0.9

assuming this is the range A1:E3. Select only the first three columns
when starting the wizard, and step through the instructions for a
stacked bar graph.

If either of the two values might be greater, stick with Jon's Line
Chart format, or at the very least an unstacked bar chart with less than
100% overlap. (the first example data setup works with both these types)

Also, start the Excel Help and type "charts" into the Help Topics box,
and select either "Chart Wizard" or "charts, creating".

--
Del Cotter
NB Personal replies to this post will send email to
(e-mail address removed),
which goes to a spam folder-- please send your email to del3 instead.