Complex Graph

D

davegb

I've been asked if I can create a horizontal bar graph in which 10
counties each have 9 areas in which they will be compared with a score
between 0 and 100%. In each of the 9 categories, there is a target
percentage. The Director wants to see a horizontal bar chart with the
results for each county in each of the 9 categories, but counties
color-coded by whether or not they met the target percentage. So if
county x scored 85% and the target was 87%, the bar would be red. If
they scored better than 87%, it would be green.

I don't think this is doable in XL. Does anyone know how to do
something like this?

Thanks.

Dave
 
T

Tim Williams

Not sure if you can format a plot in that way, but if not then you could try
using VBA to create a "plot" using shapes on a worksheet.

Tim
 
D

davegb

Not sure if you can format a plot in that way, but if not then you could try
using VBA to create a "plot" using shapes on a worksheet.

Tim







- Show quoted text -
Click to expand...

Thanks for the reply. Hadn't thought of that, but there isn't time
anyway. They need it today, of course! I'll just give them the closest
thing I can.
 
J

Jon Peltier

It's a relatively simple stacked bar chart. There are two bars for each
area, either the red or green one has value of 1, the other zero, depending
on whether the county met the goal in that area.

I've posted a zipped workbook with my data (note the formulas) and
semi-documented views of the chart each step of the way:

http://peltiertech.com/Sample/NewsGroup_RedGreenBlocks.zip

It's not really pretty, but with formatting, perhaps it could be made
useful.

- Jon
 
D

davegb

It's a relatively simple stacked bar chart. There are two bars for each
area, either the red or green one has value of 1, the other zero, depending
on whether the county met the goal in that area.

I've posted a zipped workbook with my data (note the formulas) and
semi-documented views of the chart each step of the way:

http://peltiertech.com/Sample/NewsGroup_RedGreenBlocks.zip

It's not really pretty, but with formatting, perhaps it could be made
useful.

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







- Show quoted text -
Click to expand...

John,
I certainly appreciate the effort you put into that! Unfortunately,
it's not what I was looking for. My explanation is probably not very
clear.
Thanks again.
Dave
 
J

Jon Peltier

Dave -

You want to try again? If you can describe it, I can chart it.

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


It's a relatively simple stacked bar chart. There are two bars for each
area, either the red or green one has value of 1, the other zero,
depending
on whether the county met the goal in that area.

I've posted a zipped workbook with my data (note the formulas) and
semi-documented views of the chart each step of the way:

http://peltiertech.com/Sample/NewsGroup_RedGreenBlocks.zip

It's not really pretty, but with formatting, perhaps it could be made
useful.

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







- Show quoted text -
Click to expand...

John,
I certainly appreciate the effort you put into that! Unfortunately,
it's not what I was looking for. My explanation is probably not very
clear.
Thanks again.
Dave
 
P

ptheese

I think I have a similar situation to what Dave is asking:

I need to chart three groups of two bars on a axis.
-Each group is a year, 2007, 2006, 2005 etc so we can compare data
from previous years
-in each group of two there is a profit vertical bar and a combined
ratio percentage bar, these bars are not related to each other (one
going up has no impact on the other)
-For each of these bars, there needs to be a target indicator, a small
horizontal line crossing over the bar (or above it if the target was
not hit). This line does not go across the entire chart, there is a
separate one for each individual bar.
-For the profit bar, if it crosses above the target bar, it should be
green. otherwise red.
-For the ratio/percentage bar, if it crosses above the target bar, it
should be red, otherwise green

I was able to mock this together by putting the six values in rows,
with a blank cell separating each set of bars. I then manually drew
in the target lines, and changed the color of each bar, and added text
values for the manual bars.

I will need to create 70+ of these each month, so the manual method
isn't a good long term or short term solution. Help?
 
D

davegb

Dave -

You want to try again? If you can describe it, I can chart it.

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








John,
I certainly appreciate the effort you put into that! Unfortunately,
it's not what I was looking for. My explanation is probably not very
clear.
Thanks again.
Dave
Click to expand...

Ok, I'll give it another shot. I have the 10 counties, each being
"graded" in 9 categories. I would like the vertical axis to be each
county, with a horizontal bar for each category, 40% to 100%. (This
will probably be broken into 2 bar charts printed on separate pages,
to make it more readable.) Each category has a target percentage. If
the county reaches or exceeds the target percentage, I want the entire
horizontal bar to be blue. If thecounty fell short of the target, I
want the entire horizontal bar to be red. It would be nice to have a
vertical line to represent the target value, but that might clutter
the graph up too much. So the red bars would fall short of the line,
the blue bar would run through the vertical line.

Hope that is clearer.

Thanks.
 
P

ptheese

Wow! I your instructions below were right on, thank you. I made a
few minor modifications, but this will work well.

One last question, now I want value labels for the columns and error
bars. When I turn them on for the columns, the red values are labels
that show for one series and the green values are the labels that show
for the other. I only want the non-zero value label to show. Also, I
don't see a way to have the error bar values labeled.

Is there a way to have the chart title reference a cell and display
what is typed in that cell?

Thank you very much!
 
P

ptheese

I do see how to add error bar value labels, still looking for a way to
do the columns and chart title.
 
P

ptheese

Wow! I your instructions below were right on, thank you. I made a
few minor modifications, but this will work well.

A couple fine tuning questions:

1. I want value labels for the columns and error bars. When I turn
them on for the columns, the red values are labels
that show for one series and the green values are the labels that
show
for the other, and half of all of these are zeros, can I have the zero
labels automatically suppressed?
For the error bars, I want the value labels to appear to either side
of the entire error bar, however it will only allow to put to the side
of the midpoint of the bar. If I move them manually to the side, when
I input new values will they automatically move next to the new bar
and update the text?

2. The two values I am putting in columns are actually a dollar amount
and a percentage. The issue I run into is if the dollar amount is
large (say $120M) the percentage column (ratio) is so small you can't
see it. I can manually divide these out, but each of the charts has
different sized dollar amounts, some are billions, some are millions,
and some are thousands. Is there a way to have the % (ratio) column
resize (can it use a different axis on the right that is for %, and
tick values not shown?)

3. Is there a way to have the chart title reference a cell and display
what is typed in that cell?
Thank you very much!
 
J

Jon Peltier

1. To hide zero-value labels, use a custom number format that suppresses
zero.

http://peltiertech.com/Excel/NumberFormats.html

2. You can put a series onto the secondary axis. Double click the series,
click on the Axis tab, and select Secondary. Sometimes a separate chart is
more effective than a cluttered chart with too many series on different
scales.

3. To link a chart or axis title, or data label or text box, to a cell,
select the text element, click in the formula bar, type = then select the
cell. You will see a link formula in the formula bar, like =Sheet1!$A$1.
Press Enter to establish the link.

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

special graph format 3
bar graph colors? 5
Graph Negative Values as Positive Values 0
Lookup + Max 9
excel graph transformations 1
Charting two Sets of Data 1
Displaying a horizontal quota line on a graph 3
create a floating bar graph with min and max (price for example) 2

Top