CODEWITHSUNDEEP
excelif-statementconditional-statementsaveragevba
Sebastian Koefoed
Sebastian Koefoed

Reputation: 143

VBA AverageIfs application with OR condition

My data is placed in a range. How do i get an average of a column, with multiple conditions herof an OR condition. in this example Temprange.columns(2) should be "High" or "Major"

This should be done without taking an average of two averages as per below example:

Dim a As Long
Dim b As Long
Dim ObjectKeyCounter As Long
Dim TempRange As Range
Dim TempArr As Variant
Dim MyArray As Variant

a = Application.WorksheetFunction.AverageIfs(TempRange.Columns(20), _
TempRange.Columns(1), TempArr(ObjectKeyCounter), TempRange.Columns(2), "High")
b = Application.WorksheetFunction.AverageIfs(TempRange.Columns(20), _
TempRange.Columns(1), TempArr(ObjectKeyCounter), TempRange.Columns(2), "Major")
MyArray(1, 1) = Application.WorksheetFunction.Average(a, b)

For some reason i can't find anything on the internet so i guess my way around it (and thereby my search) is wrong.

Edit: Thanks to Scott Holtzman, my solution is as follows:

Function AverageIfsOr(ByRef TempArr_Dashboard As Variant, _
 ByRef ObjectKeyCounter As Long, _
 ByRef TempArr_Data As Range, _
 ByRef ColumnToAvg As Long) As Double

    Dim vAvg() As Variant
    Dim vAvgCounter As Integer
    Dim DataRowCounter As Integer

    vAvgCounter = 0
    For DataRowCounter = 1 To TempArr_Data.Rows.Count
        If TempArr_Data(DataRowCounter, 4) = TempArr_Dashboard(ObjectKeyCounter) Then
            If TempArr_Data(DataRowCounter, 11) = "High" Or TempArr_Data(DataRowCounter, 11) = "Major" Then
                vAvgCounter = vAvgCounter + 1
                ReDim Preserve vAvg(vAvgCounter)
                vAvg(vAvgCounter) = TempArr_Data(DataRowCounter, ColumnToAvg)
            End If
        End If
    Next DataRowCounter

    If vAvgCounter = 0 Then
        AverageIfsOr = 0
    Else: AverageIfsOr = Application.WorksheetFunction.Average(vAvg)
    End If

End Function

Upvotes: 1

Views: 390

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

There may be an easier way but this works.

Option Explicit

Sub AverageIfsOr()

Dim v As Variant
Dim vAvg() As Variant

v = Range("C4:E7")

Dim i As Integer
For i = 1 To UBound(v) - 1

    If (v(i, 1) = "A" Or v(i, 1) = "B") And (v(i, 2) = "High" Or v(i, 2) = "Major") Then

        ReDim Preserve vAvg(i)
        vAvg(i) = v(i, 3)

    End If

Next

MsgBox Application.WorksheetFunction.Average(vAvg)

End Sub

You can play with the If conditions ad nauseam.

Test data is here:

enter image description here

Upvotes: 2

Related Questions