Thread: Custom average function

can this be done with Worksheet functions:

Function MyAvg(Target As Range) As String

Dim Ccell As Range, NumVals As Long, MySum As Double

  'From string to values  
  For Each Ccell In Target
      Select Case Ccell.Value
        Case ""
          'Do nothing
        Case "O"
          MySum = MySum + 1: NumVals = NumVals + 1
        Case "M"
          MySum = MySum + 2: NumVals = NumVals + 1
        Case "V"
          MySum = MySum + 3: NumVals = NumVals + 1
        Case "RV"
          MySum = MySum + 4: NumVals = NumVals + 1
        Case "G"
          MySum = MySum + 5: NumVals = NumVals + 1
        Case Else
          MySum = 0
          Exit Function
      End Select
    Next
    
    MySum = MySum / NumVals

  'From values back to Strings
  Select Case MySum
    Case 1 To 1.5
      MyAvg = "O "
    Case 1.5 To 2.5
      MyAvg = "M "
    Case 2.5 To 3.5
      MyAvg = "V "
    Case 3.5 To 4.5
      MyAvg = "RV "
    Case 4.5 To 5
      MyAvg = "G "
  End Select
  
End Function

It works like a normal AVERAGE only the 1,2,3,4,5 are now replaced by O,M,V,RV,G

Didn't work when tested - sorry

Spot on Sweep. Thx

Though I suspect you need to add a ROUND to the Average output if you wish to replicate your UDF Ricardo ?
that being said your Select Case arguments overlap so it's not quite clear if your boundaries are round related or not