Plotting or Marking Every nth Point in Visual C#.NET

Encoding QR Code in Visual C#.NET Plotting or Marking Every nth Point

26

Figure 26-5 shows a two-series line chart that has become difficult to read because the data points are too close together. (Each series now has 100 points; it s easy to imagine how much less effective this chart would become if its series grew to multiple hundreds of points.) Redrawing the series lines without markers wouldn t solve the problem in this hypothetical case; some points are required, but perhaps not all. One way around the difficulty is to plot only the points at some regular interval every fifth point, for example. Another way is to plot all the points but suppress most of the markers.

Figure 26-5. The hundreds of markers in this line chart run together and make the chart difficult to read.

Working with Chart Data To plot only every fifth point, you can take advantage of the fact that Excel normally refrains from plotting hidden points. The Plot Visible Cells Only check box determines whether Excel plots hidden points. Access this setting by choosing Tools, Options and then clicking the Chart tab. Excel selects this check box by default. Hiding groups of rows by hand is tedious in a large set, but we can use Excel s AutoFilter feature to do the hiding for us. Fill a range adjacent to the last chart column (cells C1:C100 in Figure 26-5) with the formula =MOD(ROW( ),5). Because this formula returns the remainder of the current row number divided by five, every fifth cell in the range will hold the value 0. By using the AutoFilter feature to display only those rows in which the value in column C is 0, you can make Excel plot only every fifth data point. Figure 26-6 shows the result (the MOD formulas in column C are conveniently covered by the chart). The principal disadvantage of the solution shown in Figure 26-6 is that the displayed points are now joined by straight lines, regardless of what the hidden values might be. A second disadvantage is that the category axis now asserts that we have only 21 points in our data sets which is not the case.

26

To plot all the data but display markers only for every fifth point, we could manually select points 1, 2, 3, 4, 6, 7, 8, 9, and so on, and format those points to be displayed without markers. Unfortunately, we d have to do that one point at a time clearly an odious assignment, given a large set of data.

Microsoft Office Excel 2003 Inside Out A better solution is to use a macro, such as the following, to automate the marking of intermittent data points:

Sub MarkEveryNthPoint() Dim n as Integer, s as Integer, t as Integer n = InputBox( Value of n , Mark every nth point ) Application.ScreenUpdating = False For s = 1 to ActiveChart.SeriesCollection.Count For t = 1 to ActiveChart.SeriesCollection(s).Points.Count If t Mod n <> 0 then ActiveChart.SeriesCollection(s).Points(t).MarkerStyle = xlNone End If Next t Next s End Sub

26

To use this macro, press Alt+F11 to invoke the Visual Basic Editor (VBE). In the Project window (press Ctrl+R if the Project window isn t visible), select the name of your workbook. Then choose Insert, Module. In the new module, type the code shown previously. Return to your workbook and select the chart; choose Tools, Macro, Macros, MarkEveryNthPoint, and then click Run. In the dialog box that appears, enter the value of n that you want to use, and then click OK. The macro deletes the markers from all points other than those that are even multiples of n. As Figure 26-7 shows, the macro simplifies the chart while leaving all the source data visible.