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

Here is a Demo spreadsheet on Google Docs.


Google Apps Script

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

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);
}

Microsoft Excel

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.

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

Part 1 | Part 2 | Part 3


All rights reserved