# 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

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 == "object") {
// Build string of all ratings for this skill
for (var i=0, len=range.length; i<len; i++) {
ratings += range[i] + ',';
}
} 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 == "object") {
// Build string of all ratings for this skill
for (var k=0, len2=range.length; k<len2; k++) {
ratings += range[k] + ',';
}
} 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