Clustered Bar Chart

[excelprogrammer]

Hi,
I am trying to create this chart with dates on x axis and clustered ba
chart on y axis showing

availability of equipment(Rented,Quoted,Available).

I tried to write the following macro for the above requirement but
cant get x axis to show the dates

and the bar chart on y axis does not show different colours fo
different status of equipment.

The data is as follows

A1:28882 C1:Status
A2:09/09/2005 C2:Rented
A3:09/16/2005 C3:Quoted


The macro is as follows -----------------

Sub MakeRental()

Dim i As Integer

Worksheets("Rental").Select
Worksheets("Rental").Range("A2:A3").Select
'Selection.DateFormat = "mm/dd/yyyy"

Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Rental"
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData Source:=Sheets("Rental").Range("R10")
PlotBy:=xlRows
ActiveChart.SetSourceDat
Source:=Sheets("Rental").Range("A1:A3"), PlotBy:=xlRows

With ActiveChart
.HasLegend = True
.Legend.Select
Selection.Position = xlRight
.SeriesCollection(1).Name = "=""Rented"""
With ActiveChart.SeriesCollection.NewSeries
.Name = "Quoted"
.XValues = ActiveSheet.Range("A2:A3")
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = "Available"
End With
.HasDataTable = False
.HasTitle = True
.ChartTitle.Characters.Text = "Rental Availability Chart"
End With

ActiveChart.SeriesCollection(1).Select
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 150
.HasSeriesLines = False
End With

For i = 1 To 2
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
If Worksheets("Rental").Cells(2, 3) = "Rented" Then
.ColorIndex = 4 'green
Else
If Worksheets("Rental").Cells(3, 3) = "Quoted" Then
.ColorIndex = 3 'red
End If
End If
.Pattern = xlSolid
End With
ActiveChart.ChartGroups(1).SeriesCollection(1).PlotOrder
1
Next i

With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory)
' .MinimumScale = 9 / 9 / 2005
'.MaximumScale = 9 / 25 / 2005
End With
End Sub

 

[Jon Peltier]

Excel doesn't know what you want to plot, and neither to I. You need
some numerical values to plot, not text labels.

I don't really know what you want, but maybe it's something like this:

Rented Quoted
09/09/2005 1 0
09/16/2005 0 1

Plot as a stacked column chart, with series in columns. Format the
Rented and Quoted series with the colors you associate with each. Go to
Chart Options on the Chart menu, and on the Axes tab, change Automatic
to Category for the X axis. Double click one of the bars, and on the
options tab change gap width to zero.

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

 

[excelprogrammer]

Thanks for your post,I followed your instructions about plotting as a
stacked column chart, I got a chart with two bars on the x axis, one
for Rented and one for Quoted, what i require is one bar showing both
Rented and Quoted status for the date range in the given data.

The original data was as follows

A1:28882 C1:Status
A2:09/09/2005 C2:Rented
A3:09/16/2005 C3:Quoted

This means that 28882,which is an equipment unit , has status Rented
from date 9 Sep to 15 Sep and has status Quoted from 16 Sep onwards
till end of month. This has to be shown in the chart with dates on x
axis and availability of equipment(Rented,Quoted,Available) on y axis
in different colours.

I understand that excel needs numerical values to plot, hence i am
trying to write a vba macro which will somehow manipulate and show
desired colours which i am trying using .ColorIndex.

Thanks and Regards

 

[Jon Peltier]

What you need then is a gantt chart sort of approach. It's complicated
by the fact that you have to accommodate multiple conditions, often
repeated and often in any order, during the timespan of the chart. Your
data would look like this for two items. In the following, the first
item starts on 9/9, is quoted for 0 days, then is rented for 7 days,
then is quoted for the rest of the period. The second item starts on
9/9, is quoted for 7 days, rented for another 7, then quoted to the end
of the period.

Start Quoted Rented Quoted Rented etc.
28882 09/09/2005 0 7 X1
28883 09/09/2005 7 7 X2
etc.

Start is the first date on which an item has any status (could be the
start of the chart), and is a date. The rest of the items are durations.
X1 and X2 are for the duration to the end of the chart. This is a
stacked horizontal bar. Make the bar for the Start series invisible (no
border, no fill), format all Quoted series the same, and all Rented
series the same. Each like-named series is a separate series; there's no
way to have the same series recur in this manner.

For more on this approach, see:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343
http://peltiertech.com/Excel/Charts/GanttChart.html

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

 

[excelprogrammer]

Thanks for your reply,Jon. I tried using the data table format that you
have given and I was able to create a much better chart than what i
have been able to do so before. On the x axis though, i get numbers and
not dates as desired. I would like to get dates on the x axis.

I went through the article that you have mentioned in your post

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

The article states the following -

Quick Trick: Even though Excel expects a number (for example, April 1,
2004 = 38078) in the axis scale parameter boxes of a value axis, you
can type in a date, and Excel will convert it for you. This works if
you are entering times, as well.

When i tried changing the - Value (Y) axis scale - i changed the
minimum to 9/9/2005 , maximum to 30/9/2005, it gave me an error - Your
entry cannot be used. An integer or decimal number may be required.

Thanks and Regards,

 

[Jon Peltier]

Try 9/30/2005 for the maximum. You may have confused Excel. Maybe it
isn't smart enough to recognize a European date format in the dialog
that's expecting a number.

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