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