AVERAGEIF IN CODE, BUT A SELECTION OF CELLS

[SQUIDD]

Hi

So i have a few scattered cells that i need to get the average of them

The below works until one of these figures is a "0", then it messes with my average.

Set myrng = Union(Cells(2, 2), Cells(4, 2))
my_average = Application.WorksheetFunction.Average(myrng)

so i wanted to modify this to averageif, but i am not having an sucsess, perhaps i am chasing something that will not work.

and help or pointers would be great.

thanks

dave

 

[kevin9999]

There's (at least) a couple of different ways you could do this using VBA. The first option below calculates the AverageIf. If your range is simply cells B2 & B4 - and there is nothing but a blank cell in B3 - then the second option is simpler. Neither option has error trapping if both cells contain zeros.

Sub Squidd1()
    Dim myrng As Range, c As Range, mycount As Long, my_average As Long
    Set myrng = Union(Cells(2, 2), Cells(4, 2))
    
    For Each c In myrng
        If c <> "0" Then mycount = mycount + 1
    Next
    
    my_average = Application.Sum(myrng) / mycount
    MsgBox my_average
End Sub

Sub Squidd2()
    Dim myrng As Range, my_average As Long
    Set myrng = Range(Cells(2, 2), Cells(4, 2))
    
    my_average = Application.AverageIf(myrng, "<>0")
    MsgBox my_average
End Sub