Calculation of AverageIF/AverageIFS based on conditions RRS feed

  • Question

  • I need to calculate AverageIF/AverageIFS for Column B & Column D based on the three groups for each sheet in my workbook.

    Group1 - Column C is less than $E$2

    Group2 - Column C is between $E$2 & $E$3

    Group3 - Column C is greater than $E$3

    Each group have different number of observations. for each group I need to calculate percentiles and the average respective to their percentiles. Below is for example. sheet1 contain Data from Column A - Column G and as per Group1 condition I have 240 rows in Group1 that are less than $E$2 so I claculated this manually in Column H (H2:H10), Column I (I2:I11) and Column J(J2:J11) Column G contain 9 values from 0.1,0.2, ... 0.9

    Column H2=PERCENTILE($D$2:$D$240,$G2) .... Column H10 =PERCENTILE($D$2:$D$240,$G10)
    Column I2=AVERAGEIF($D$2:$D$240,"<"&$H$2,$D$2:$D$240)
    Column I3=AVERAGEIFS($D$2:$D$240,$D$2:$D$240,">="&$H$2,$D$2:$D$240,"<"&$H$3)
    ...Column I11'=AVERAGEIF($D$2:$D$240,">"&$H$10,$D$2:$D$240)
    Column J2=AVERAGEIF($D$2:$D$240,"<"&$H$2,$B$2:$B$240)
    Column J3=AVERAGEIFS($B$2:$B$240,$D$2:$D$240,">="&$H$2,$D$2:$D$240,"<"&$H$3)
    ...Column J11=AVERAGEIF($D$2:$D$240,">"&$H$10,$B$2:$B$240)

    Similarly I have to change the row numbers in above formulas as per Group 2 conditions. 241 - 582 and for last group 583 - 828.

    Each sheet have different numbers of Rows so I am looking for any VBA code that can automate this process as I have 214 sheets and I need to perform this on each sheet.

    I am trying to add formulas but unable to add further 

    Sub AddingDecile()
        Dim sheetNo As Long
      Dim Wkst As Excel.Worksheet
        For sheetNo = 1 To ThisWorkbook.Worksheets.Count
        Set Wkst = ThisWorkbook.Worksheets(sheetNo)
        Call AddHeadings(Wkst)
        Call MyFormat(Wkst)
        Call Enter_Formula1(Wkst)
        'Call Enter_Formula2(Wkst)
        'Call Enter_Formula3(Wkst)
    End Sub
    Sub AddHeadings(ByRef WK As Excel.Worksheet)
     WK.Range("G1").Value = "Cutoff Points"
     WK.Range("H1").Value = "Decile"
     WK.Range("I1").Value = "Average VaR"
     WK.Range("J1").Value = "Average Monthly Return"
    End Sub
    Sub MyFormat(ByRef WK As Excel.Worksheet)
    '   Capture last row
        Dim myLastRow As Long
        myLastRow = WK.Range("G10")
    '   Autofill columns BS
        Columns("G").NumberFormat = "@"
        WK.Range("G2").FormulaR1C1 = "'0.1"
        WK.Range("G2").AutoFill Destination:=WK.Range("G2:G10"), Type:=xlFillSeries
    End Sub
    Sub Enter_Formula1(ByRef WK As Excel.Worksheet)
    WK.Range("H2:H10").Value = "=PERCENTILE($D$2:$D$240,$G2)"
    End Sub
    'Sub Enter_Formula1(ByRef WK As Excel.Worksheet)
    'WK.Range("H11:H20").Value = "=PERCENTILE($D$:$D$---,$G2)"  'Column D range depend on CF
    'End Sub
    'Sub Enter_Formula1(ByRef WK As Excel.Worksheet)
    'WK.Range("H21:H30").Value = "=PERCENTILE($D$:$D$---,$G2)" 'Column D range depend on CF
    'End Sub

    Monday, August 17, 2020 11:12 PM

All replies

  • Change your Formula adding macros to check the last filled row of each sheet, like in the code below. I have assumed that by "'Column D range depend on CF" that you mean column CF and not Conditional Formatting.

    Sub Enter_Formula1(ByRef WK As Excel.Worksheet)

    Dim lngR As Long

    lngR = WK.Cells(WK.Rows.Count, "CF").End(xlUp).Row

    WK.Range("H11:H20").Formula = "=PERCENTILE($D$2:$D$" & lngR & ",$G2)" 

    End Sub

    Tuesday, August 25, 2020 3:56 PM