Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Plot On Chart/Graph As Zigzag Pattern

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Plot On Chart/Graph As Zigzag Pattern

    Hi!
    I have the following list of values which I want to plot as a zigzag. But when i create a line chart from them, the lines connect the zeros and the non-zero points - while I want the lines to connect only the non-zero points - so that there is a zigzag-like pattern shown...I wonder if this can be done..Thanks!

    the Data:

    0
    0
    0
    0
    0
    1.4343
    0
    0
    0
    0
    0
    0
    0
    0
    1.4332
    0
    0
    0
    0
    0
    0
    0
    0
    1.4318
    0
    0
    0
    0
    0
    0
    0
    0
    0
    1.4521
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    1.5153
    0
    0
    0
    0
    0
    0
    1.5336
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    1.55
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    1.5431
    0
    0
    0
    0
    0
    0
    0
    0
    0
    1.5472
    0
    0
    0
    0
    0
    0
    0
    0
    1.5404
    0
    0
    0
    0
    0
    0
    0


    Dima

  • #2
    Re: Plot On Chart/Graph As Zigzag Pattern

    If you want to plot the non-zero values then put an autofilter on your data, set it to Custom > does not equal 0.

    Charts only plot visible data.

    BTW shouldn't your chart have a second data series, as the non-zero values are not equally spaced?

    Comment


    • #3
      Re: Plot On Chart/Graph As Zigzag Pattern

      hi,
      i numbered data and make scattered chart.
      have a look at attached file.
      regards/junho lee
      Attached Files

      Comment


      • #4
        Re: Plot On Chart/Graph As Zigzag Pattern

        thanks..but i need such a graph without modifying the data series...((

        Comment


        • #5
          Re: Plot On Chart/Graph As Zigzag Pattern

          Code:
          Option Explicit
          Option Base 0
          Sub create_chart()
          '--------------------set data source
          Dim data, i As Long, num As Long, data1, data2
          data = Range("a1:a" & Range("a65536").End(xlUp).Row)
          ReDim data1(num)
          ReDim data2(num)
          For i = 1 To UBound(data, 1)
          If data(i, 1) > 0 And IsNumeric(data(i, 1)) Then ReDim Preserve data1(num) _
          : ReDim Preserve data2(num) _
          : data1(num) = i: data2(num) = data(i, 1): num = num + 1
          Next
          '----------------------make chart
          Charts.Add
              ActiveChart.ChartType = xlXYScatter
              ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
              ActiveChart.SeriesCollection.NewSeries
              ActiveChart.SeriesCollection(1).XValues = WorksheetFunction.Transpose(data1)
              ActiveChart.SeriesCollection(1).Values = WorksheetFunction.Transpose(data2)
              ActiveChart.Axes(xlCategory).Select
              With Selection.Border
                  .Weight = xlHairline
                  .LineStyle = xlAutomatic
              End With
              With Selection
                  .MajorTickMark = xlOutside
                  .MinorTickMark = xlNone
                  .TickLabelPosition = xlNextToAxis
              End With
              Selection.TickLabels.AutoScaleFont = False
              With Selection.TickLabels.Font
                  .Size = 11
              End With
              ActiveChart.Axes(xlValue).Select
              With Selection.Border
                  .Weight = xlHairline
                  .LineStyle = xlAutomatic
              End With
              With Selection
                  .MajorTickMark = xlOutside
                  .MinorTickMark = xlNone
                  .TickLabelPosition = xlNextToAxis
              End With
              Selection.TickLabels.AutoScaleFont = False
              With Selection.TickLabels.Font
                  .Size = 11
              End With
              ActiveChart.SeriesCollection(1).Select
              With Selection.Border
                  .Weight = xlThin
                  .LineStyle = xlAutomatic
              End With
              With Selection
                  .MarkerBackgroundColorIndex = xlAutomatic
                  .MarkerForegroundColorIndex = xlAutomatic
                  .MarkerStyle = xlAutomatic
                  .Smooth = False
                  .MarkerSize = 5
                  .Shadow = False
              End With
              ActiveChart.Legend.Select
              Selection.Delete
          End Sub
          regards/junho lee

          Comment


          • #6
            Re: Plot On Chart/Graph As Zigzag Pattern

            If you can't modify your data then can you add some further calculations alongside it?

            The attached gives you what you want without changing or filtering your data.
            Attached Files

            Comment


            • #7
              Re: Plot On Chart/Graph As Zigzag Pattern

              thanks....let me check it out!

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X