Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Easy charting question (hopefully)
Hi All,
This should be a fairly simple one but I can not find through Google or Microsoft on how to accomplish it. My rows of data have 14 columns. One of those columns I would like to do a count on, so for example the COLUMN of data looks like: Apples Apples Apples Oranges Pears Pears Zuchini I want a chart that shows a count for each one, so there would be 3 apples, 1 oranges, 2 pears, and 1 zuchini. This would then be in a bar chart. Does that make sense? Thanks for your help, SD |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Easy charting question (hopefully)
Perhaps this:
Sub Test() Dim r As Range, c As Range Dim cht As Chart Dim s As Series Dim ws As Worksheet Dim coll As Collection Dim i As Integer Dim val As Integer, MaxVal As Integer Set coll = New Collection Set ws = Sheets("Inventory") With ws Set r = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)) End With On Error Resume Next For Each c In r.Cells coll.Add c.Value, c.Value Next On Error GoTo 0 Set cht = ws.ChartObjects(1).Chart With cht For i = 1 To .SeriesCollection.Count cht.SeriesCollection(1).Delete Next For i = 1 To coll.Count Set s = .SeriesCollection.NewSeries val = Application.CountIf(r, coll(i)) s.Values = val MaxVal = IIf(MaxVal < val, val, MaxVal) s.Name = coll(i) s.Border.LineStyle = xlNone s.HasDataLabels = True With s.Points(1).DataLabel .Font.Color = vbRed .Text = coll(i) End With Next With .Axes(xlCategory) .HasTitle = True .AxisTitle.Characters.Text = "Produce Inventory" End With With .Axes(xlValue) .HasTitle = True .AxisTitle.Characters.Text = "Tonnes" .MaximumScale = 1.5 * MaxVal .MinimumScale = 0 End With End With End Sub Regards, Greg " wrote: Hi All, This should be a fairly simple one but I can not find through Google or Microsoft on how to accomplish it. My rows of data have 14 columns. One of those columns I would like to do a count on, so for example the COLUMN of data looks like: Apples Apples Apples Oranges Pears Pears Zuchini I want a chart that shows a count for each one, so there would be 3 apples, 1 oranges, 2 pears, and 1 zuchini. This would then be in a bar chart. Does that make sense? Thanks for your help, SD |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Easy charting question (hopefully)
You won't need to set the axis titles every time. So the following could be
simplified. Change: With .Axes(xlCategory) .HasTitle = True .AxisTitle.Characters.Text = "Produce Inventory" End With With .Axes(xlValue) .HasTitle = True .AxisTitle.Characters.Text = "Tonnes" .MaximumScale = 1.5 * MaxVal .MinimumScale = 0 End With To: With .Axes(xlValue) .MaximumScale = 1.5 * MaxVal .MinimumScale = 0 End With Greg "Greg Wilson" wrote: Perhaps this: Sub Test() Dim r As Range, c As Range Dim cht As Chart Dim s As Series Dim ws As Worksheet Dim coll As Collection Dim i As Integer Dim val As Integer, MaxVal As Integer Set coll = New Collection Set ws = Sheets("Inventory") With ws Set r = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)) End With On Error Resume Next For Each c In r.Cells coll.Add c.Value, c.Value Next On Error GoTo 0 Set cht = ws.ChartObjects(1).Chart With cht For i = 1 To .SeriesCollection.Count cht.SeriesCollection(1).Delete Next For i = 1 To coll.Count Set s = .SeriesCollection.NewSeries val = Application.CountIf(r, coll(i)) s.Values = val MaxVal = IIf(MaxVal < val, val, MaxVal) s.Name = coll(i) s.Border.LineStyle = xlNone s.HasDataLabels = True With s.Points(1).DataLabel .Font.Color = vbRed .Text = coll(i) End With Next With .Axes(xlCategory) .HasTitle = True .AxisTitle.Characters.Text = "Produce Inventory" End With With .Axes(xlValue) .HasTitle = True .AxisTitle.Characters.Text = "Tonnes" .MaximumScale = 1.5 * MaxVal .MinimumScale = 0 End With End With End Sub Regards, Greg " wrote: Hi All, This should be a fairly simple one but I can not find through Google or Microsoft on how to accomplish it. My rows of data have 14 columns. One of those columns I would like to do a count on, so for example the COLUMN of data looks like: Apples Apples Apples Oranges Pears Pears Zuchini I want a chart that shows a count for each one, so there would be 3 apples, 1 oranges, 2 pears, and 1 zuchini. This would then be in a bar chart. Does that make sense? Thanks for your help, SD |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Easy charting question (hopefully)
Put a title on that column, "Food". Select the range, and from the Data
menu, crate a pivot table. Put the Food field into the Row area, and another copy of it into the Data area. The result looks like this (hope it pastes okay): Count of Food Food Total Apples 3 Oranges 1 Pears 2 Zuchini 1 Grand Total 7 You can chart this data. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ wrote in message oups.com... Hi All, This should be a fairly simple one but I can not find through Google or Microsoft on how to accomplish it. My rows of data have 14 columns. One of those columns I would like to do a count on, so for example the COLUMN of data looks like: Apples Apples Apples Oranges Pears Pears Zuchini I want a chart that shows a count for each one, so there would be 3 apples, 1 oranges, 2 pears, and 1 zuchini. This would then be in a bar chart. Does that make sense? Thanks for your help, SD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function question (probably easy) | Excel Discussion (Misc queries) | |||
easy question | Excel Worksheet Functions | |||
Easy function question | Excel Discussion (Misc queries) | |||
Easy formula Question | Excel Worksheet Functions | |||
easy question .... i think | Excel Discussion (Misc queries) |