+ Reply to Thread
Results 1 to 6 of 6

Clustered Bar Chart

  1. #1
    Registered User
    Join Date
    09-02-2005
    Posts
    4

    Clustered Bar Chart

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

    availability of equipment(Rented,Quoted,Available).

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

    and the bar chart on y axis does not show different colours for 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.SetSourceData 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

    ---------------------------------------------------


    Regards,

  2. #2
    Jon Peltier
    Guest

    Re: Clustered Bar Chart

    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 wrote:

    > Hi,
    > I am trying to create this chart with dates on x axis and clustered bar
    > chart on y axis showing
    >
    > availability of equipment(Rented,Quoted,Available).
    >
    > I tried to write the following macro for the above requirement but i
    > cant get x axis to show the dates
    >
    > and the bar chart on y axis does not show different colours for
    > 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.SetSourceData
    > 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
    >
    > ---------------------------------------------------
    >
    >
    > Regards,
    >
    >


  3. #3
    Registered User
    Join Date
    09-02-2005
    Posts
    4
    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

  4. #4
    Jon Peltier
    Guest

    Re: Clustered Bar Chart

    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/P...cle.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 wrote:

    > 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
    >
    >


  5. #5
    Registered User
    Join Date
    09-02-2005
    Posts
    4
    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/P...cle.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,

  6. #6
    Jon Peltier
    Guest

    Re: Clustered Bar Chart

    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/
    _______


    excelprogrammer wrote:

    > 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/P...cle.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,
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1