I hopesomeone can edit this fuction so i can use it including an if criteria

[tonywatsonhelp]

Hi Everyone,
I have this function i use a lot,

Function LogAverage(ParamArray AveCells() As Variant) As Double
   
    Dim out As Double
    Dim cel As Range
    Dim count, i As Long
    
    count = 0
    out = 0

    For i = LBound(AveCells) To UBound(AveCells)
    
        If TypeName(AveCells(i)) = "Range" Then
            
            For Each cel In AveCells(i)
                
                If IsEmpty(cel) Then
                    'skip blank cells
                Else
                    out = out + 10 ^ (cel.Value / 10)
                    count = count + 1
               End If
               
            Next cel
            
        Else
        
            If IsEmpty(AveCells(i)) Then
                'skip blank cells
            Else
                out = out + 10 ^ (AveCells(i) / 10)
                count = count + 1
            End If
            
        End If
    
    Next i

    LogAverage = 10 * Log(out / count) / Log(10)

End Function

which allows me to get a log average over a range,
I'm woundering if it would be possible to change it so it gives the log average over the range of cells that match the cell next to it, (Like sumif and countif do)

so instead of just
=LogAverage(C26:C500)
Maybe
in C2 it would read

=LogAverageIf(A26:A500,A2,C26:C500) (With A2 being the critiria and A26:A500, the criteria range)

please help if you can
Thanks
Tony

 

[mikenelena]

Maybe something like this:

Function LogAverageIf(CriteriaRange As Range, Criteria As Variant, ParamArray AveCells() As Variant) As Double

    Dim out As Double
    Dim cel As Range
    Dim count, i As Long
    
    count = 0
    out = 0

    For i = LBound(AveCells) To UBound(AveCells)
    
        If TypeName(AveCells(i)) = "Range" Then
            
            For Each cel In AveCells(i)
                
                If IsEmpty(cel) Then
                    'skip blank cells
                Else
                    If cel.Offset(0, -1).Value = Criteria Then
                        out = out + 10 ^ (cel.Value / 10)
                        count = count + 1
                    End If
               End If
               
            Next cel
            
        Else
        
            If IsEmpty(AveCells(i)) Then
                'skip blank cells
            Else
                out = out + 10 ^ (AveCells(i) / 10)
                count = count + 1
            End If
            
        End If
    
    Next i

    LogAverageIf = 10 * Log(out / count) / Log(10)

End Function

This new function takes in two additional parameters: CriteriaRange and Criteria. CriteriaRange is the range of cells that contains the criteria, and Criteria is the actual criteria you want to match against.

The function loops through each cell in the AveCells range and checks if the value in the cell next to it (offset by one column to the left) matches the Criteria. If it does, then it includes that cell in the calculation of the log average.

You can use this function just like your example by entering =LogAverageIf(A26:A500,A2,C26:C500) in cell C2.

 

[tonywatsonhelp]

Thank you mike,
that is great
Tony

 

[mikenelena]

Thank you mike,
that is great
Tony

You're welcome. Glad it worked for you Tony. Don't forget to mark your post as Solved if you're all set. Have a great day.