Complex Graph
 

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

Complex Graph
 

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

Complex Graph
 

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.

Complex Graph
 

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

Complex Graph
 

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

Complex Graph
 

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

Complex Graph
 

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?

Complex Graph
 

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!

On Feb 18, 2:52*pm, "Jon Peltier"
wrote:
Check this blog entry (watch the line wrap). I made up some data and built
thechartthat 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...-stacked-clust...

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

wrote in message

...



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

I need tochartthree 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 verticalbarand a combined
ratio percentagebar, 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 atargetindicator, a small
horizontal line crossing over thebar(or above it if thetargetwas
not hit). *This line does not go across the entirechart, there is a
separate one for each individualbar.
-For the profitbar, if it crosses above thetargetbar, it should be
green. *otherwise red.
-For the ratio/percentagebar, if it crosses above thetargetbar, 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 thetargetlines, and changed the color of eachbar, 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?- Hide quoted text -

- Show quoted text -

Complex Graph
 

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



On Feb 26, 2:13*pm, wrote:
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 errorbarvalues labeled.

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

Thank you very much!

On Feb 18, 2:52*pm, "Jon Peltier"
wrote:



Check this blog entry (watch the line wrap). I made up some data and built
thechartthat 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...-stacked-clust...

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

wrote in message

...

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

I need tochartthree 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 verticalbarand a combined
ratio percentagebar, 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 atargetindicator, a small
horizontal line crossing over thebar(or above it if thetargetwas
not hit). *This line does not go across the entirechart, there is a
separate one for each individualbar.
-For the profitbar, if it crosses above thetargetbar, it should be
green. *otherwise red.
-For the ratio/percentagebar, if it crosses above thetargetbar, 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 thetargetlines, and changed the color of eachbar, 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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Complex Graph
 

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!


On Feb 18, 2:52 pm, "Jon Peltier"
wrote:
Check this blog entry (watch the line wrap). I made up some data and built
thechartthat 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...-stacked-clust...

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

wrote in message

...



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

I need tochartthree 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 verticalbarand a combined
ratio percentagebar, 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 atargetindicator, a small
horizontal line crossing over thebar(or above it if thetargetwas
not hit). This line does not go across the entirechart, there is a
separate one for each individualbar.
-For the profitbar, if it crosses above thetargetbar, it should be
green. otherwise red.
-For the ratio/percentagebar, if it crosses above thetargetbar, 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 thetargetlines, and changed the color of eachbar, 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?- Hide quoted text -

- Show quoted text -

Complex Graph
 

On Feb 18, 8:42*pm, "Jon Peltier"
wrote:
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" wrote in message

...



On Feb 15, 10:59 am, "Jon Peltier"
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" 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

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

- Show quoted text -

Wow! I'm impressed. You know XL charting as well or better than anyone
I've ever met. Thanks for the help!

Complex Graph
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


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


On Feb 18, 2:52 pm, "Jon Peltier"
wrote:
Check this blog entry (watch the line wrap). I made up some data and
built
thechartthat 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...-stacked-clust...

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

wrote in message

...



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

I need tochartthree 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 verticalbarand a combined
ratio percentagebar, 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 atargetindicator, a small
horizontal line crossing over thebar(or above it if thetargetwas
not hit). This line does not go across the entirechart, there is a
separate one for each individualbar.
-For the profitbar, if it crosses above thetargetbar, it should be
green. otherwise red.
-For the ratio/percentagebar, if it crosses above thetargetbar, 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 thetargetlines, and changed the color of eachbar, 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?- Hide quoted
text -

- Show quoted text -