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" <(E-Mail Removed)> wrote in message
news:175d1116-01d9-4d9f-accc-(E-Mail Removed)...
> 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" <daveg...@comcast.net> wrote in message
>
> news:175d1116-01d9-4d9f-accc-(E-Mail Removed)...
>
>
>
> > 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" <(E-Mail Removed)> wrote in message
news:175d1116-01d9-4d9f-accc-(E-Mail Removed)...
> 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" <jonxlmv...@SPAMpeltiertech.com>
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" <daveg...@comcast.net> wrote in message
>
> news:175d1116-01d9-4d9f-accc-(E-Mail Removed)...
>
>
>
> > 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" <(E-Mail Removed)> wrote in message
news:86b51c14-1037-436b-be4e-(E-Mail Removed)...
On Feb 15, 8:35 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
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" <daveg...@comcast.net> wrote in message
>
> news:175d1116-01d9-4d9f-accc-(E-Mail Removed)...
>
>
>
> > 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?

Check this blog entry (watch the line wrap). I made up some data and built
the chart that I think you asked for. To do your many charts, you could make
a template, and then each month load the template and bang out the charts.

http://peltiertech.com/WordPress/200...-with-targets/

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


<(E-Mail Removed)> wrote in message
news:6fdfc1af-ece7-4f4d-98f8-(E-Mail Removed)...
>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?

On Feb 15, 10:59 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> 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" <daveg...@comcast.net> wrote in message
>
> news:86b51c14-1037-436b-be4e-(E-Mail Removed)...
> On Feb 15, 8:35 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> 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" <daveg...@comcast.net> wrote in message
>
> >news:175d1116-01d9-4d9f-accc-(E-Mail Removed)...
>
> > > 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

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.

Okay, I gave it another shot:

http://peltiertech.com/Sample/NewsGr...reenBlocks.zip

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


"davegb" <(E-Mail Removed)> wrote in message
news:32120b32-4c20-4ef1-af7b-(E-Mail Removed)...
> On Feb 15, 10:59 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>> 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" <daveg...@comcast.net> wrote in message
>>
>> news:86b51c14-1037-436b-be4e-(E-Mail Removed)...
>> On Feb 15, 8:35 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
>> 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" <daveg...@comcast.net> wrote in message
>>
>> >news:175d1116-01d9-4d9f-accc-(E-Mail Removed)...
>>
>> > > 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
>
> 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.