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

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


"davegb" wrote in message
...
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

On Feb 14, 8:22*pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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

"davegb" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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.

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/NewsGr...reenBlocks.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
_______


"davegb" wrote in message
...
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

On Feb 15, 8:35*am, "Jon Peltier"
wrote:
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/NewsGr...reenBlocks.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
_______

"davegb" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

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
_______


"davegb" wrote in message
...
On Feb 15, 8:35 am, "Jon Peltier"
wrote:
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/NewsGr...reenBlocks.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
_______

"davegb" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

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?