Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 18
Default How to create a min-max bar chart in excel 2007 ?

I have a list of 24 set of data. Each category have a min-max set in $.

When I'm using stacked bar chart and define the min value with no fill, I
get the look I'm looking for. The problem is with the X-axis which shows the
total amount of min+max instead of using only the max series as data set.

After looking around for quite a good time, I figured somebody here might
have stumbled on that problem.

Thanks

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 99
Default How to create a min-max bar chart in excel 2007 ?

Hi Junkyard,

I'm not sure that I understand your question. However, have you tried using
Up/Down-bars to plot a min/max chart?

To do this
- select the two data series (minimum and maximum values)
- use the Insert ribbon to add a line chart with no markers
- select the chart and use the Chart Tools - Layout ribbon to show Up/Down
bars
- format each series to show no line (in line color) and no marker

This shows just the Up/Down bars which look like a floating column chart.
The series remain simple line charts, so it should be easy for you to format
the axis as desired.

Ed Ferrero
www.edferrero.com

I have a list of 24 set of data. Each category have a min-max set in $.

When I'm using stacked bar chart and define the min value with no fill, I
get the look I'm looking for. The problem is with the X-axis which shows
the total amount of min+max instead of using only the max series as data
set.

After looking around for quite a good time, I figured somebody here might
have stumbled on that problem.

Thanks


  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default How to create a min-max bar chart in excel 2007 ?

Hi,

In order to get the Max point of the bar in the correct place you will
need to re calculate the max values used. So in another column plot the
formula which would be MaxValue - MinValue.

Or use a clustered bar chart. Format the Min series to have no border
and a fill colour that matches the plot area fill colour.
Set the Overlap value to 100.
reverse the plot order so Min is plotted on top of Max.

The only draw back with the overlap approach is the obscuring of gridlines.

Cheers
Andy


Junkyard Engineer wrote:
I have a list of 24 set of data. Each category have a min-max set in $.

When I'm using stacked bar chart and define the min value with no fill,
I get the look I'm looking for. The problem is with the X-axis which
shows the total amount of min+max instead of using only the max series
as data set.

After looking around for quite a good time, I figured somebody here
might have stumbled on that problem.

Thanks

  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 18
Default How to create a min-max bar chart in excel 2007 ?

Thanks Ed. Didn't saw that option.

This approach have 2 drawbacks as I can see it :

1. there's no possibility to add automatic label to show $ values min/max on
each side of the bars
2. the bars are always vertical. This type of graph is best shown sideways,
i.e. using horizontal bars.

or maybe there's more hidden options ?


"Ed Ferrero" a écrit dans le message de
...
Hi Junkyard,

I'm not sure that I understand your question. However, have you tried
using Up/Down-bars to plot a min/max chart?

To do this
- select the two data series (minimum and maximum values)
- use the Insert ribbon to add a line chart with no markers
- select the chart and use the Chart Tools - Layout ribbon to show Up/Down
bars
- format each series to show no line (in line color) and no marker

This shows just the Up/Down bars which look like a floating column chart.
The series remain simple line charts, so it should be easy for you to
format the axis as desired.

Ed Ferrero
www.edferrero.com

I have a list of 24 set of data. Each category have a min-max set in $.

When I'm using stacked bar chart and define the min value with no fill, I
get the look I'm looking for. The problem is with the X-axis which shows
the total amount of min+max instead of using only the max series as data
set.

After looking around for quite a good time, I figured somebody here might
have stumbled on that problem.

Thanks



  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 18
Default How to create a min-max bar chart in excel 2007 ?

Thanks Andy. Good trick !

In fact, I used the max-min values and selected min and that 3rd series to
get real numbers on the axis. But label values should be edited manually to
enter correct max values.

This is a pretty standard graph, I can't imagine that the guys at MS didn't
saw that coming.



"Andy Pope" a écrit dans le message de
...
Hi,

In order to get the Max point of the bar in the correct place you will
need to re calculate the max values used. So in another column plot the
formula which would be MaxValue - MinValue.

Or use a clustered bar chart. Format the Min series to have no border and
a fill colour that matches the plot area fill colour.
Set the Overlap value to 100.
reverse the plot order so Min is plotted on top of Max.

The only draw back with the overlap approach is the obscuring of
gridlines.

Cheers
Andy


Junkyard Engineer wrote:
I have a list of 24 set of data. Each category have a min-max set in $.

When I'm using stacked bar chart and define the min value with no fill, I
get the look I'm looking for. The problem is with the X-axis which shows
the total amount of min+max instead of using only the max series as data
set.

After looking around for quite a good time, I figured somebody here might
have stumbled on that problem.

Thanks




  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,489
Default How to create a min-max bar chart in excel 2007 ?

Have a look at Rob Bovey's chart labeler to make changing the data
labels easier.
http://www.appspro.com/Utilities/ChartLabeler.htm

Cheers
Andy

Junkyard Engineer wrote:
Thanks Andy. Good trick !

In fact, I used the max-min values and selected min and that 3rd series
to get real numbers on the axis. But label values should be edited
manually to enter correct max values.

This is a pretty standard graph, I can't imagine that the guys at MS
didn't saw that coming.



"Andy Pope" a écrit dans le message de
...

Hi,

In order to get the Max point of the bar in the correct place you will
need to re calculate the max values used. So in another column plot
the formula which would be MaxValue - MinValue.

Or use a clustered bar chart. Format the Min series to have no border
and a fill colour that matches the plot area fill colour.
Set the Overlap value to 100.
reverse the plot order so Min is plotted on top of Max.

The only draw back with the overlap approach is the obscuring of
gridlines.

Cheers
Andy


Junkyard Engineer wrote:

I have a list of 24 set of data. Each category have a min-max set in $.

When I'm using stacked bar chart and define the min value with no
fill, I get the look I'm looking for. The problem is with the X-axis
which shows the total amount of min+max instead of using only the max
series as data set.

After looking around for quite a good time, I figured somebody here
might have stumbled on that problem.

Thanks



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pasting a chart from Excel 2007 to Word 2007 trouble Vegas Charts and Charting in Excel 5 September 16th 08 07:37 AM
how do I create a chart in excel 2007 with two axies ther00k Charts and Charting in Excel 1 December 21st 07 02:58 PM
Cannot create charts in Excel 2007 Whiz_In_Ache Excel Discussion (Misc queries) 0 August 21st 07 03:25 PM
Create combo chart 2007 Johnny G Charts and Charting in Excel 2 July 23rd 07 09:02 PM
Excel 2007 - cannot create an outline 6538 Excel Discussion (Misc queries) 1 July 3rd 07 12:45 AM


All times are GMT +1. The time now is 11:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"