vba to calculate correlations from averages based on header criteria

[cjcass]

Hi,
Please have a look at the pic below and it's hopefully self-explanatory to a degree.
I'm looking for a macro I can run that will go to all the wkshts (tab names) in Col W in the wksht 'Results' and work out Correlations based on the Header Criteria in Cols AA and AB. So using the example the first part of the calculation is to work out the average across Row4 for the yellow criteria (6.5), and then the average for the green (4.0), then do the same the whole way down the table and give the Correlation for all the averages in each wksht. In this example for the wksht '1' the result is 0.43. The tables in each wksht cover the range A3:GR50000 but not every table will be that size, most will be <10000 rows down (at the moment).
Can this be achieved?
Any help much appreciated
PS. I've assumed a solution using cell formulas is probably not an option.

 

[cjcass]

has anyone got any possible code for this?
thanks.

 

[offthelip]

You can do that fairly easliy with equations: put this in row 4 of column U
=iferror(AVERAGE(A4,E4,M4),"")
then put this in row 4 of column W and copy both down to the maximum row you expect on any sheet

=iferror(AVERAGE(B5,D5,H5,J5),"")

Then put this somewhere at the top e.g. U2
=CORREL(U4:U10000,V4:V10000)
If the maximum number of rows is more that 10000 then increase that number. The correl function ignore blanks so provide the number is big enough it will work on all the sheets, so now copy these tow columns to all sheets.
Now you can add the results for each sheet with a simple =Sheet2!U2, etc

 

[cjcass]

Thanks for your time and input on this but I'm after a vba solution that will rapidly interrogate all wkshts against the variable criteria in Cols AA & AB and update X7:X26 without having to input formulas down the side of tables across all wkshts.

 

[offthelip]

This should get you started I haven't tested it because I don't have your workbook:
I have used a couple of extra columns at the end of the data to do the correlation.

Sub test()
Dim outarr As Variant


Worksheets("Results").Select


tabnames = Range(Cells(7, 23), Cells(26, 24))
Variables = Range(Cells(7, 7), Cells(26, 19))
For i = 1 To 19
 With Worksheets(tabnames(i, 1))
   lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
   ReDim outarr(1 To lastrow, 1 To 2)
   
    Datar = Range(.Cells(1, 1), .Cells(lastrow, 200)) ' Colums A to Gr
    ' initialise output
    For j = 4 To lastrow
       outarr(j, 1) = 0
       outarr(j, 2) = 0
    Next j
    cnt1 = 0
    cnt2 = 0
    For j = 4 To lastrow
    For k = 1 To 13
     If Variables(k, 1) <> "" Then
      outarr(j, 1) = outarr(j, 1) + Datar(j, Variables(k, 1))
      cnt1 = cnt1 + 1
     End If
     If Variables(k, 2) <> "" Then
      outarr(j, 2) = outarr(j, 2) + Datar(j, Variables(k, 2))
      cnt2 = cnt2 + 1
     End If
    Next k
    ' calc average
    outarr(j, 1) = outarr(j, 1) / cnt1
    outarr(j, 2) = outarr(j, 2) / cnt2
    Next j
    
    Range(.Cells(1, 201), .Cells(lastrow, 202)) = outarr
    Range(.Cells(2, 201), .Cells(2, 201)).Formula = "=CORREL(GS4:GS" & lastrow & ",GT4:GT" & Lastrown & ")"
    
    tabnames(i, 2) = Range(.Cells(2, 201), .Cells(2, 201))
 End With
Next i




Range(Cells(7, 23), Cells(26, 24)) = tabnames


End Sub

 

[cjcass]

Hi,
Many thanks for this.
I was getting ?NAME in the cells that hosts the CORREL formula, so I spotted the typo towards the end of the code 'Lastrown' and corrected that.
So now it all populates nicely but the averages are not correct.
For example, in Row 4 of the highlighted criteria above I'm getting averages of;
Criteria1 = 5.4 and Criteria2 = 5.3 instead of;
Criteria1 = 6.5 and Criteria2 = 4.0
Any thoughts?
Rgds,

 

[offthelip]

I suggest you step through the code using debug to check that the summation of the cells is picking up the correct cells. This line:

outarr(j, 1) = outarr(j, 1) + Datar(j, Variables(k, 1))

And then check that the cnt1 has got the correct number in it.

 

[offthelip]

I have spotted and error in the average calculation I initailsed the counts in the wrong place.

    For j = 4 To lastrow  
    cnt1 = 0
    cnt2 = 0
    For k = 1 To 13

 

[cjcass]

a lot of the averages are the same in both columns, as follows:
0.384615385 0.384615385
0.38974359 0.38974359
0.365384615 0.365384615
0.343891403 0.343891403
am not sure how to step through the code as you have described, can you give me a steer?

 

[cjcass]

ok, which section does that replace?
thanks