Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Problems with XY-Scatter
Excel 2003
Hi, I have set up an employee table with around 1000 employees in a worksheet. Each record consists of the following columns: Name Grade (1-5) Trend (1-3) I want Grade on the y axis and Trend on the x axis. Each dot in the chart represents 1 employee. To accomplish this I loop through the table adding each employee (the code below is just a simple example): Sub PopulateChart() Dim I1 As Integer Dim sc As Object ActiveSheet.ChartObjects("Diagram 6").Activate For I1 = 2 To 1000 Step 1 Set sc = ActiveChart.SeriesCollection.NewSeries sc.XValues = "=Sheet1!R" & I1 & "C3" sc.Values = "=Sheet1!R" & I1 & "C4" sc.Name = "=Sheet1!R" & I1 & "C2" Next I1 End Sub This works fine but the problem is that there's a limit and I can only add 255 series to the chart. Normally you don't want to see all 1000 employees plotted in the chart but it's useful to get an overview and for finding groups of ppl that deviate somehow from the rest of the population. The next step would for example be to apply a filter to only view the employees with average grade (3) and a negative trend.(1). Is there another way to populate the chart so all employees are fitted? Or could I maybe use another chart to accomplish the same thing? Regards Pete |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Problems with XY-Scatter
Okay, make it simple. You only need one series, and labels made by one of
these free and powerful Excel utilities: Rob Bovey's Chart Labeler, http://appspro.com/Utilities/ChartLabeler.htm John Walkenbach's Chart Tools, http://www.j-walk.com/ss/excel/files/charttools.htm Download and install one, then restart Excel. Put the trend data to the left of the Grade data. Select these two columns and make an XY chart. Trend, in the first column, will be X. Now use the utility you downloaded to add the labels from the first column as data labels on this series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "PO" <h wrote in message ... Excel 2003 Hi, I have set up an employee table with around 1000 employees in a worksheet. Each record consists of the following columns: Name Grade (1-5) Trend (1-3) I want Grade on the y axis and Trend on the x axis. Each dot in the chart represents 1 employee. To accomplish this I loop through the table adding each employee (the code below is just a simple example): Sub PopulateChart() Dim I1 As Integer Dim sc As Object ActiveSheet.ChartObjects("Diagram 6").Activate For I1 = 2 To 1000 Step 1 Set sc = ActiveChart.SeriesCollection.NewSeries sc.XValues = "=Sheet1!R" & I1 & "C3" sc.Values = "=Sheet1!R" & I1 & "C4" sc.Name = "=Sheet1!R" & I1 & "C2" Next I1 End Sub This works fine but the problem is that there's a limit and I can only add 255 series to the chart. Normally you don't want to see all 1000 employees plotted in the chart but it's useful to get an overview and for finding groups of ppl that deviate somehow from the rest of the population. The next step would for example be to apply a filter to only view the employees with average grade (3) and a negative trend.(1). Is there another way to populate the chart so all employees are fitted? Or could I maybe use another chart to accomplish the same thing? Regards Pete |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Problems with XY-Scatter
Thanks Jon! Exactly what I was looking for.
Regards Pete "Jon Peltier" skrev i meddelandet ... Okay, make it simple. You only need one series, and labels made by one of these free and powerful Excel utilities: Rob Bovey's Chart Labeler, http://appspro.com/Utilities/ChartLabeler.htm John Walkenbach's Chart Tools, http://www.j-walk.com/ss/excel/files/charttools.htm Download and install one, then restart Excel. Put the trend data to the left of the Grade data. Select these two columns and make an XY chart. Trend, in the first column, will be X. Now use the utility you downloaded to add the labels from the first column as data labels on this series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "PO" <h wrote in message ... Excel 2003 Hi, I have set up an employee table with around 1000 employees in a worksheet. Each record consists of the following columns: Name Grade (1-5) Trend (1-3) I want Grade on the y axis and Trend on the x axis. Each dot in the chart represents 1 employee. To accomplish this I loop through the table adding each employee (the code below is just a simple example): Sub PopulateChart() Dim I1 As Integer Dim sc As Object ActiveSheet.ChartObjects("Diagram 6").Activate For I1 = 2 To 1000 Step 1 Set sc = ActiveChart.SeriesCollection.NewSeries sc.XValues = "=Sheet1!R" & I1 & "C3" sc.Values = "=Sheet1!R" & I1 & "C4" sc.Name = "=Sheet1!R" & I1 & "C2" Next I1 End Sub This works fine but the problem is that there's a limit and I can only add 255 series to the chart. Normally you don't want to see all 1000 employees plotted in the chart but it's useful to get an overview and for finding groups of ppl that deviate somehow from the rest of the population. The next step would for example be to apply a filter to only view the employees with average grade (3) and a negative trend.(1). Is there another way to populate the chart so all employees are fitted? Or could I maybe use another chart to accomplish the same thing? Regards Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XY Scatter-problems with large data records | Excel Discussion (Misc queries) | |||
Problems creating a line chart or scatter graph | Charts and Charting in Excel | |||
XY Scatter | Excel Worksheet Functions | |||
xy scatter help | Charts and Charting in Excel | |||
Scatter Graph - Data Label Problems | Charts and Charting in Excel |