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 Scriptvar 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);
}
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 ExcelFunction 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
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
(http://cheetah.example.com:81/listen.html)
