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```
Oct 28, 2022 in Others 165 views

## 1 answer to this question.

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```
• 53,520 points

## Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

## Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

## Calculate the number of days between a cell and today in excel?

Use the DATEDIF function when you want ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE