Volleyball Player Efficiency Rating, Part 3: Calculations

The following tables are examples of how to record the performance stats into a spreadsheet. The hyphen represents a separation between sets. This is not required and can be omitted, but it does tend to help with data entry. It can also be helpful for extending this spreadsheet at a future date to drill down into individual set statistics.

Sample Stats Tabulation
Player Serve Attack Block Pass Dig Set
1 libero 333-3 321-223133 322323-02220
3 outside 201 24304-1244 041 322-0233 2330-1123
4 setter 32 4,10,7,11,12,6,10,9,11,12

Sample Calculations:
Player Serve Attack Block Pass Dig Set PER
1 libero 0.750 0.750 0.682 0.719
3 outside 0.250 0.667 0.417 0.750 0.625 0.608
4 setter 0.875 0.767 0.785
Team: 0.536 0.667 0.417 0.750 0.679 0.767 0.681

Sample Formulas:
Player Serve Attack Block Pass Dig Set PER
1 libero =CalcRating(I2) =CalcRating(J2) =CalcRating(K2) =CalcRating(L2, 1) =CalcRating(M2, 1) =CalcRating(N2, 2) =CalcPer(I2, 0, J2, 0, K2, 0, L2, 1, M2, 1, N2, 2)
3 outside =CalcRating(I3) =CalcRating(J3) =CalcRating(K3) =CalcRating(L3, 1) =CalcRating(M3, 1) =CalcRating(N3, 2) =CalcPer(I3, 0, J3, 0, K3, 0, L3, 1, M3, 1, N3, 2)
4 setter =CalcRating(I4) =CalcRating(J4) =CalcRating(K4) =CalcRating(L4, 1) =CalcRating(M4, 1) =CalcRating(N4, 2) =CalcPer(I4, 0, J4, 0, K4, 0, L4, 1, M4, 1, N4, 2)
Team: =CalcRating(I2:I4) =CalcRating(J2:J4) =CalcRating(K2:K4) =CalcRating(L2:L4, 1) =CalcRating(M2:M4, 1) =CalcRating(N2:N4, 2) =CalcPer(I2:I5, 0, J2:J5, 0, K2:K5, 0, L2:L5, 1, M2:M5, 1, N2:N5, 2)

Here is a Demo spreadsheet on Google Docs.

The following is the source code for the CalcRating() and CalcPer() functions used in the Google spreadsheet. You can add this code via the menu: Tools -> Script Editor

Google Apps Script
var maxRating = 4;

function _calcRating(data, ratings, skillType) {
  if (skillType == 2) {
    ratings = ratings.replace("-", ",");
    ratings = ratings.split(',');
  }

  for (var i=0, len=ratings.length; i<len; i++) {
    ch = ratings[i];
   
    // Ignore non-numeric values such as '-'
    if (ch != parseInt(ch)) {
      continue;
    }

    rat = parseInt(ch);

    // Convert to (0-4) point scale
    if (skillType == 1) {
      // There is no 2 in Pass and Dig
      if (rat == 2 || rat == 3) {
        rat++;
      }
    } else if (skillType == 2) {
      // (0-12) becomes (0-4)
      rat /= 3.0;
    }

    data.cnt++;
    data.tot += rat;
  }
 
  data.rating = (data.tot / data.cnt / maxRating);
 
  return data;
}

function _disp(data) {
  if (data.cnt == 0) {
    return ' ';
  }

  //return '.' + Math.round(data.rating * 1000) + ':' + data.cnt;
  return data.rating;
}

function CalcRating(range, skillType) {
  var data = {
     tot: 0
    ,cnt: 0
  };

  var ratings = '';
   // if this is a range list (J2:J13)
  if (typeof range[0] == "object") {
    // Build string of all ratings for this skill
    for (var i=0, len=range.length; i<len; i++) {
      ratings += range[i][0] + ',';
    }
  } else {
    // A single ratings cell (J2)
    ratings = range;
  }
  data = _calcRating(data, ratings, skillType);
 
  return _disp(data);
}

function CalcPer() {
  var data = {
     tot: 0
    ,cnt: 0
  };
 
  // Loop over argument list
  for (var i=0, len=CalcPer.arguments.length; i<len; i+=2) {
    range = CalcPer.arguments[i];
    skillType = CalcPer.arguments[i+1]

    var ratings = '';
    // if this is a range list (J2:J13)
    if (typeof range[0] == "object") {
      // Build string of all ratings for this skill
      for (var k=0, len2=range.length; k<len2; k++) {
        ratings += range[k][0] + ',';
      }
    } else {
      // A single ratings cell (J2)
      ratings = range;
    }

    data = _calcRating(data, ratings, skillType);
  }
 
  return _disp(data);
}

The following is the source code for the CalcRating() and CalcPer() functions used in the Microsoft Excel version. You can add this module by pressing Alt-F11 to bring up the Microsoft Visual Basic Editor. Select Insert -> Module and paste in this code.

Microsoft Excel
Function xCalcRating(tot As Double, cnt As Integer, ratings As String, skillType As Integer)
    If Len(ratings) = 0 Then
        Exit Function
    End If
   
    Dim ratingsList() As String
    Dim rat As Double
   
    If skillType = 2 Then
        ratingsList = Split(ratings, ",")
    Else
        ReDim ratingsList(1 To Len(ratings))
        For i = 1 To Len(ratings)
            ratingsList(i) = Mid(ratings, i, 1)
        Next i
    End If
   
    For Each v In ratingsList
        If IsNumeric(v) Then
            rat = Val(v)
            If skillType = 1 Then
                If rat = 2 Or rat = 3 Then
                    rat = rat + 1
                End If
            End If
            If skillType = 2 Then
                rat = rat / 3
            End If
   
            cnt = cnt + 1
            tot = tot + rat
        End If
    Next
End Function

Function xDisp(tot As Double, cnt As Integer)
    If cnt = 0 Then
        xDisp = ""
        Exit Function
    End If

    'xDisp = Str(Math.Round(tot / cnt / 4 * 1000)) & ":" & cnt
    xDisp = tot / cnt / 4
End Function

Function CalcRating(range As range, Optional skillType As Integer)
    Dim ratings As String
    Dim tot As Double, cnt As Integer, rat As Integer
    Dim res As Variant

    tot = 0
    cnt = 0
   
    For i = 1 To range.Rows.Count
        ratings = range(i, 1)
        res = xCalcRating(tot, cnt, ratings, skillType)
    Next i
   
    CalcRating = xDisp(tot, cnt)
End Function

Function CalcPer(ParamArray arguments() As Variant)
    Dim tot As Double, cnt As Integer
    Dim res As Variant
    Dim ratings As String
   
    tot = 0
    cnt = 0
       
    For i = LBound(arguments) To UBound(arguments)
        ratings = ""
        For k = 1 To arguments(i).Rows.Count
            ratings = ratings & arguments(i)(k, 1) & ","
        Next k

        res = xCalcRating(tot, cnt, ratings, Val(arguments(i + 1)))

        i = i + 1
    Next i
   
    CalcPer = xDisp(tot, cnt)
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *