If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 Whe=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).Plo tOrder = 1 Next i With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory) ' .MinimumScale = 9 / 9 / 2005 '.MaximumScale = 9 / 25 / 2005 End With End Sub --------------------------------------------------- Regards, -- excelprogrammer ------------------------------------------------------------------------ excelprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=26923 View this thread: http://www.excelforum.com/showthread...hreadid=467460 |
#2
|
|||
|
|||
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 Whe=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).Plo tOrder = 1 Next i With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory) ' .MinimumScale = 9 / 9 / 2005 '.MaximumScale = 9 / 25 / 2005 End With End Sub --------------------------------------------------- Regards, |
#3
|
|||
|
|||
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 -- excelprogrammer ------------------------------------------------------------------------ excelprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=26923 View this thread: http://www.excelforum.com/showthread...hreadid=467460 |
#4
|
|||
|
|||
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
|
|||
|
|||
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, -- excelprogrammer ------------------------------------------------------------------------ excelprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=26923 View this thread: http://www.excelforum.com/showthread...hreadid=467460 |
#6
|
|||
|
|||
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, |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dual Axis Clustered Stack Bar Chart | Guenther | Charts and Charting | 5 | November 19th, 2004 12:49 PM |
Selecting Charts in a Macro | Herman Merman | Charts and Charting | 1 | August 18th, 2004 12:25 AM |
Chart menu visible property | Sandy V | Charts and Charting | 8 | May 17th, 2004 01:39 PM |
Clustered Column Chart with Second Axis | Jon Peltier | Charts and Charting | 0 | April 10th, 2004 04:56 AM |
Styles for chart | Debra Dalgleish | Charts and Charting | 1 | October 3rd, 2003 12:27 PM |