Creating a function in excel VBA to calculate the average point in a circular set of numbers

Question

Please be patient with me; I'm still really new at this. In order to calculate the average of a set of values, I am trying to write a function. The group of numerals is actually a cog's teeth. Damage or stoppage is noted on teeth in a clockwise rotation, so damage at teeth 7 and 23 would be at 7 and 23 teeth from the starting tooth. The primary tooth is usually tooth 1 (identified as painted). When you calculate a normal average, there is an anomaly because the average stoppage at teeth 3, 4, and 33 would actually be 1, NOT 14.33 as per a regular average.I did the math to determine the average, and by average I mean a set of circular numbers that is closer to the median. I increase each value in the range by one and then use the MOD function to determine the difference between the highest and lowest values. It only takes a few steps to deduct the incremented number from the new average once I've determined the starting position of the shortest difference. Most likely, a table would describe it better.

As you can see, tooth 1, which is the average less the increment of the first group of numbers with the least difference, is the true average or median. The code I now use to run through and perform these calculations is returning a value# error, but I have very little expertise with custom functions and don't know where to begin to fix the problem. Any pointers would be greatly appreciated, and a solution would be excellent. 

Public Function AVGDISTCALC(rng As Range)
'Determines the average distance of a number of distances on a 37 tooth wheel.
Dim x As Integer
Dim i As Integer
Dim avg As Integer
Dim diff As Integer
Dim Arr() As Variant
Dim r As Long
Dim c As Long
Application.ScreenUpdating = False

    'Write the range to an array.
    Arr = rng
    'Cycle through each increment on the 37 tooth wheel.
    diff = 38
    For i = 1 To 37
    Arr = rng
        'For each increment calculate the min and max of the range.
        For r = 1 To UBound(Arr, 1)
            For c = 1 To UBound(Arr, 2)
                If (Arr(r, c) + i) Mod 37 = 0 Then
                    Arr(r, c) = 37
                Else
                    Arr(r, c) = (Arr(r, c) + i) Mod 37
                End If
            Next c
        Next r
        If WorksheetFunction.Max(Arr) - WorksheetFunction.Min(Arr) < diff Then
            diff = WorksheetFunction.Max(Arr) - WorksheetFunction.Min(Arr)
            avg = WorksheetFunction.Average(Arr)
            x = i
        End If
    Next i
    
    AVGDISTCALC = avg - x
    
End Function

Answer 1

I used the following code to determine the average of a circular group of numbers. I hope that anyone else who is having a similar problem can use this example. Simply adjust the MOD value as necessary if you require a different number of cog teeth.

Public Function AVGDISTCALC(rng As Range)
'Determines the average distance of a number of distances on a 37 tooth wheel.
Dim x As Integer
Dim i As Integer
Dim avg As Integer
Dim diff As Integer
Dim Arr() As Variant
Dim r As Long
Dim c As Long
Application.ScreenUpdating = False

    'Write the range to an array.
    Arr = rng
    'Cycle through each increment on the 37 tooth wheel.
    diff = 38
    For i = 1 To 37
    Arr = rng
        'For each increment calculate the min and max of the range.
        For r = 1 To UBound(Arr, 1)
            For c = 1 To UBound(Arr, 2)
                If (Arr(r, c) + i) Mod 37 = 0 Then
                    Arr(r, c) = 37
                Else
                    Arr(r, c) = (Arr(r, c) + i) Mod 37
                End If
            Next c
        Next r
        If WorksheetFunction.Max(Arr) - WorksheetFunction.Min(Arr) < diff Then
            diff = WorksheetFunction.Max(Arr) - WorksheetFunction.Min(Arr)
            avg = WorksheetFunction.Average(Arr)
            x = i
        End If
    Next i
    
    Select Case avg - x
    Case 0
        AVGDISTCALC = 37
    Case Is > 0
        AVGDISTCALC = avg - x
    Case Is < 0
        AVGDISTCALC = (avg - x) + 37
    End Select
    
End Function