Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

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

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Posts
    527
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Last edited by cjcass; Feb 22nd, 2018 at 10:33 AM.

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    527
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to calculate correlations from averages based on header criteria

    has anyone got any possible code for this?
    thanks.

  3. #3
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    435
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Oct 2011
    Posts
    527
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    435
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by offthelip; Feb 23rd, 2018 at 10:06 AM.

  6. #6
    Board Regular
    Join Date
    Oct 2011
    Posts
    527
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    435
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    435
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by offthelip; Feb 23rd, 2018 at 11:44 AM.

  9. #9
    Board Regular
    Join Date
    Oct 2011
    Posts
    527
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    Board Regular
    Join Date
    Oct 2011
    Posts
    527
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to calculate correlations from averages based on header criteria

    ok, which section does that replace?
    thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •