Plot chart from every 10cols. 5pairs of x, y data 1

[MatthewBell]

Hopefully that title was clear.

I have loads of test data in a sheet. Its in pairs of columns for each series:
A=Freq1(x1) B=Amp1(y1) C=Freq2(x2) D=Amp2(y2) E=Freq3(x3) etc

What I need to do is get charts for every 5 pairs of columns so chart one has 5 series. BvsA DvsC ... JvsI
Then another chart for LvsK NvsM etc
Until there are no longer any columns of data.
The number of rows is the same for the 2cols in a pair but different for every pair. A&B have 212 rows C&D have 243rows etc.

I've recorded a macro for producing a chart so I have some code for most of the chart settings but its the loop with setting the series collections that I'm a little stumped on.

The next bit is to get one chart with a series for the average of each batch of 5column pairs. The rows dont line up for x values so I'm guessing I need to interpolate to get common x values before averaging. If any one has a clever way around this please tell. The first bit is more important though.

Many thanks, I hope its clear.

Matt

 

[SkipVought]

Mat,

On bit 2:
it might help to post sample data for 2 5-series sets and explain the result that you would expect from this "average".

On bit 1:
You need to loop across columns by 5*2, something like this

Sub LoadSeries()
   For n = 1 To Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Columns.Count / 5
      Set cht = ActiveSheet.ChartObjects(n).Chart
      c = (n - 1) * 5 + 1
      r = Cells(1, c).End(xlDown).Row
      For j = 1 To 5
         cht.SeriesCollection.Add Source:=ActiveSheet.Range(Cells(1, c), Cells(r, c + 4))
      Next
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

oops...

need to divide by 10

Sub LoadSeries()
   For n = 1 To Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Columns.Count / 10
    'add you chart here
      Set cht = ActiveSheet.ChartObjects(n).Chart
      c = (n - 1) * 5 + 1
      r = Cells(1, c).End(xlDown).Row
      For j = 1 To 5
         cht.SeriesCollection.Add Source:=ActiveSheet.Range(Cells(1, c), Cells(r, c + 4))
      Next
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

Matt,

Sorry, I rushed my solution. I think that this one is alot closer to what you may want.

Sub LoadSeries()
   For n = 1 To Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Columns.Count / 10
      Set cht = ActiveSheet.ChartObjects(n).Chart
      c = (n - 1) * 10 + 1
      r = Cells(1, c).End(xlDown).Row
      For j = 1 To 5
         cht.SeriesCollection.Add Source:= _
            ActiveSheet.Range(Cells(1, c + (j - 1) * 2), Cells(r, c + 1 + (j - 1) * 2))
      Next
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[MatthewBell]

Cheers skip. That did it after a few alterations. I had to step r inside the J loop and define the Xvalues and Values individually but its working really well now.
I'd like to challenge the averaging now which I do believe will be a git.
I've inserted some example data below. The data is in pairs of columns amp vs Hz.

(Hz) (amp) (Hz) (amp) (Hz) (amp)
48.93 0.77 48.74 1.14 48.74 0.84
48.64 0.65 48.44 0.86 48.44 0.64
48.35 0.58 48.06 0.64 48.06 0.53
48.06 0.58 47.67 0.62 47.67 0.50
47.76 0.64 47.38 0.73 47.38 0.58
47.47 0.76 46.99 0.88 46.99 0.71
47.18 0.88 46.60 1.04 46.69 0.85
46.89 1.01 46.31 1.16 46.40 0.96
46.60 1.09 45.92 1.16 46.01 0.99
46.31 1.09 45.53 1.01 45.72 0.89
46.11 0.97 45.24 0.75 45.33 0.71
45.72 0.78 44.85 0.48 45.04 0.53

I need to find averages for the amp data at say 0.5Hz intervals. The only way I could see of doing it was to interpolate to get data at every selected Freq and then pop that in a sheet with one freq column and all the interpolated amp cols. Take an average from each batch of 5 cols and plot those against each other.
I don't know how to do this in VBA though.

If you can achieve this I'll be amazed. If not thanks for the help so far. It's gonna save me hours already.

Regards

Matt

 

[SkipVought]

Can you write spreadsheet formulas to do what you want? If so, add new columns for these averages.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244