# Calculation of AverageIF/AverageIFS based on conditions

• ### 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)
Next
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