# 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)

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

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