Chart showing ranges of prices of different items?

[Dream]

Greetings,

I am trying to create a chart similar to the one available at the following
link:

http://earthtrends.wri.org/images/ethanol_production_cost_small.jpg

It shows on the Y-axis different items. On the X-axis, the price in USD
0..1...2...3...4...5...6...7...and so on.

Than, for each product I need to plot its range of prices. For example, for
item 1, its price ranges from 3 to 5 dollar. so it should be a horizontal
line or bar extending from 3 to 5.

Could you please explain to me how to do it? By the way, Any idea what these
types of charts are called?

Thanks in advance,

 

[Yves Dhondt]

There might be better options but I would use a stacked bar chart and hide
the first data entry to get the offsets correct.

Consider the data to be in 1 column, e.g.

A1 : Ethanol from sugar cane (Brazil)
A2 : 3.0 <= open value
A3 : 0.8 <= difference between open and close value

B1 : Ethanol from corn (US)
B2 : 4.1
B3 : 2.7

Then just generate a stacked bar chart. You should get 2 items per series.
If you format the first item to have no border and no fill color, your chart
should look the way you want.

The following macro formats does the hiding automatically. You might have to
adjust the range in your case of course:

====================================
Sub OpenCloseChart()

' Create the chart and add the data.
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$B$3")
ActiveChart.ChartType = xlBarStacked

' Hide the first series.
ActiveChart.SeriesCollection(1).Select
Selection.Border.ColorIndex = xlColorIndexNone
Selection.Interior.ColorIndex = xlColorIndexNone

' Hide the legend
ActiveChart.SetElement (msoElementLegendNone)

' Set the X axis scale.
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MajorUnit = 1

End Sub

====================================

Yves

 

[Dream]

Thank you for your reply. It is very helpful.

There might be better options but I would use a stacked bar chart and hide
the first data entry to get the offsets correct.

Consider the data to be in 1 column, e.g.

A1 : Ethanol from sugar cane (Brazil)
A2 : 3.0 <= open value
A3 : 0.8 <= difference between open and close value

B1 : Ethanol from corn (US)
B2 : 4.1
B3 : 2.7

Then just generate a stacked bar chart. You should get 2 items per series.
If you format the first item to have no border and no fill color, your chart
should look the way you want.

The following macro formats does the hiding automatically. You might have to
adjust the range in your case of course:

====================================
Sub OpenCloseChart()

' Create the chart and add the data.
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$B$3")
ActiveChart.ChartType = xlBarStacked

' Hide the first series.
ActiveChart.SeriesCollection(1).Select
Selection.Border.ColorIndex = xlColorIndexNone
Selection.Interior.ColorIndex = xlColorIndexNone

' Hide the legend
ActiveChart.SetElement (msoElementLegendNone)

' Set the X axis scale.
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MajorUnit = 1

End Sub

====================================

Yves



.

 

[kiwiangus]

Hi Dream,
I recreated the chart as shown in the picture, but there are 2 tricks.
1. In one row (say row 2, we'll assume row 1 has the headings) display the
"Low" prices and then in another row (row 3) display the DIFFERENCE not the
"High" price. Eg. If a low price was $0.55 and the high $0.88, then show as
your "High" $0.33 i.e. the difference between the two prices. Now select the
information and create your chart using Horizontal Bars.

Trick 2:
Once you have created your chart, select Series 1 (the low figures),
right-click and select 'Format Data Series', then select 'No Fill' and 'No
Borders' and this series will disappear and the remaining information should
resemble what you are looking for. Well it did on my chart!

I don't know what this type of chart is called, but vertically it's called a
'Floating Chart' so I assume it's a something similar.

This is the first time I've responsed to a problem, so hope it works.
Cheers