# Thread: vba to calculate correlations from averages based on header criteria

1. ## vba to calculate correlations from averages based on header criteria

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.

2. ## Re: vba to calculate correlations from averages based on header criteria

has anyone got any possible code for this?
thanks.

3. ## Re: vba to calculate correlations from averages based on header criteria

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

4. ## Re: vba to calculate correlations from averages based on header criteria

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.

5. ## Re: vba to calculate correlations from averages based on header criteria

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.
Code:
```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```

6. ## Re: vba to calculate correlations from averages based on header criteria

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,

7. ## Re: vba to calculate correlations from averages based on header criteria

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:
Code:
`outarr(j, 1) = outarr(j, 1) + Datar(j, Variables(k, 1))`
And then check that the cnt1 has got the correct number in it.

8. ## Re: vba to calculate correlations from averages based on header criteria

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

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

9. ## Re: vba to calculate correlations from averages based on header criteria

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?

10. ## Re: vba to calculate correlations from averages based on header criteria

ok, which section does that replace?
thanks