Serve streak

This spreadsheet is useful for analyzing the team before the game and seeing which player is the most dangerous in the serve zone. It can also be used to monitor the performance of your own team.

How does it work?

This spreadsheet can be filtered by the serve type (first serve, second serve, third serve, and the rest), and you can choose the type of visualization in orange cells on the top of the sheet. It is divided by the starting six players and bench players. The starting six players are filled automatically, but the bench players and their team prefix must be put in manually. The tables show data for the total number of serves and first, second, and third serves. There is also a section where you can find information about more serves in the row (4th, 5th, etc.), which is called “The Rest”. Each section contains four columns displaying the total attempts of serve, the percentage of aces and errors, and the percentage of points won after this serve.

In this example, you can see that the most serves were played by player #12 (Kaczmarek) and #15 (Smith). Player #12 had the longest series because we can see that nine times, he played at least three serves in a row. Player #5 had the longest series because his number of attempts in the last section was the largest (6 serves). Player #10 achieved the highest percentage of points won after his serve (Bednorz, 44%) who was also the most successful while playing second (56%) and third serves (57%). Player #19 had the lowest percentage of points won after his serve (25%) and the shortest series (Pashytsky), who never served more than three times in a row.

Below this table, there are courts where you can see directions of the first, second, and third serves.

In this example, we can see that the player (#20 Smith D.) served mostly from zone 7 to zones 6 and 5 in his first serves. Similarly, in his second attempt, he served in this direction. In his third attempt, he mostly served to zone 6. This information allows you to be better prepared for the opponent’s serve and give better tips to your passing players.

Important formulas in this spreadsheet:

=VSCOUNT(VSOR(VSAND(VSCELL("(1)S(2)",$A5,$E$1),"*S;R-1;NOT"),VSCELL("(1)S(2);PR0:0",$A5,$E$1)))

This formula counts the first serves of the player from cell A5 while the serve type was determined in cell E1.

=VSPER(VSOR(VSAND(VSCELL("(1)S(2)#",$A5,$E$1),"*S;R-1;NOT"),VSCELL("(1)S(2)#;PR0:0",$A5,$E$1)))

This formula gives the percentage of aces played in the first serve in a row by the player from cell A5, while the serve type was determined in cell E1.

=VSCOUNT(VSOR(VSAND(VSCELL("(1)S(2);WR-0",$A5,$E$1),"*S;R-1;NOT"),VSCELL("(1)S(2);WR-0;PR0:0",$A5,$E$1)))/I5

This formula counts the percentage of points won after the first serve in a row by the player from cell A5, while the serve type was determined in cell E1.

=VSCOUNT(VSOR(VSAND(VSCELL("(1)S(2)",$A5,$E$1),"*S;R-1","*S;R-2;NOT"),VSAND(VSCELL("(1)S(2)",$A5,$E$1),"*S;PR0:0;R-1","*S;PR0:0;NOT")))

This formula counts the second serves of the player from cell A5 while the serve type was determined in cell E1.

=VSPER(VSOR(VSAND(VSCELL("(1)S(2)#",$A5,$E$1),"*S;R-1","*S;R-2;NOT"),VSAND(VSCELL("(1)S(2)#",$A5,$E$1),"*S;PR0:0;R-1","*S;PR0:0;NOT")))

This formula counts the percentage of aces played in the second serve in a row by the player from cell A5, while the serve type was determined in cell E1.

=ROUND(VSCOUNT(VSOR(VSAND(VSCELL("(1)S(2);WR-0",$A5,$E$1),"*S;R-1","*S;R-2;NOT"),VSAND(VSCELL("(1)S(2);WR-0",$A5,$E$1),"*S;PR0:0;R-1","*S;PR0:0;NOT")))/$M5,2)

This formula counts the percentage of points won after the second serve in a row by the player from cell A5, while the serve type was determined in cell E1. The result is rounded to the second decimal place.

=VSCOUNT(VSOR(VSAND(VSCELL("(1)S(2)",$A5,$E$1),"*S;R-1","*S;R-2","*S;R-3;NOT"),VSAND(VSCELL("(1)S(2)",$A5,$E$1),VSCELL("(1)S(2);PR0:0;R-2",$A5,$E$1),"*S;PR0:0;NOT")))

This formula counts the third serves of the player from cell A5 while the serve type was determined in cell E1.

=VSPER(VSOR(VSAND(VSCELL("(1)S(2)#",$A5,$E$1),"*S;R-1","*S;R-2","*S;R-3;NOT"),VSAND(VSCELL("(1)S(2)#",$A5,$E$1),VSCELL("(1)S(2);PR0:0;R-2",$A5,$E$1),"*S;PR0:0;NOT")))

This formula counts the percentage of aces played in the third serve in a row by the player from cell A5, while the serve type was determined in cell E1. 

=ROUND(VSCOUNT(VSOR(VSAND(VSCELL("(1)S(2);WR-0",$A5,$E$1),"*S;R-1","*S;R-2","*S;R-3;NOT"),VSAND(VSCELL("(1)S(2);WR-0",$A5,$E$1),VSCELL("(1)S(2);PR0:0;R-2",$A5,$E$1),"*S;PR0:0;NOT")))/$Q5,2)

This formula counts the percentage of points won after the third serve in a row by the player from cell A5, while the serve type was determined in cell E1. The result is rounded to the second decimal place.