Logarithmic averaging preset function in excel - using ranges as input values

Question

I need to add my own function for logarithmic averaging to excel but i'm not sure how to have a range of values as an input value or how to make count the number of values in a given range. I have a small amount of experience with programming.

The formula i usually use in excel and am looking to implement as a pre-set function is the following:

=10*LOG(SUM(10^('range of values'/10)/'number of values in the range'))

Can anyone help me out?

Answer 1

You can try this, you may need to adjust to account for blank cells or non-text in the range

Function TestUDF(rngValues As Range) As Double

    Dim lSumofValues As Long
    Dim lCountofValues As Long
    Dim rngLoop As Range

    lSumofValues = 0
    lCountofValues = rngValues.Count 'Get count of values in items in range

'Add up the values in the range
    For Each rngLoop In rngValues
        lSumofValues = lSumofValues + rngLoop.Value
    Next
'Perform Calculation
    TestUDF = 10 * Log((10 ^ (lSumofValues / 10) / lCountofValues))

End Function

And then simply enter =TestUDF(A1:A18) in a cell to use it.