Serve (play by play)

This sheet contains a play-by-play analysis of the team’s serve. It is suitable for when you want to get all the essential information about the setter and hitters, and it is a valuable spreadsheet that you can use on the bench. 

How does it work?

In the orange cell, you can select the game you want to analyze, which is useful during pre-game analysis. You can see the game’s name/label on the top of the sheet. Above each table, you also have the number of the set and the score. See the example table below: SET 2 (25:19).

For each set, there is a table that shows the players and their serve attempts chronologically. It allows you to see how many balls the player served in a row and what the results were. It helps to analyze how the opponent team passed the serves of particular players, who was the most effective server, and how they served in the most important phase of the set. This table shows the efficiency of the servers over time, helps prepare tactics for reception, and/or makes decisions about starting the rotation of our team based on the serve performance of the opponent players. 

This example shows that the most balls in a row were served by player #11 (Sliwka A.). Most of his serves had the effect “!”, which means that opponents struggled to pass the ball, and Sliwka’s team had time to prepare for blocking or digging the ball and conduct a counterattack. Player #9 (Leon W.) also had a long series of serves, but in his case, we can see that the serve quality was dropping, and the opponents were enhancing their passing quality over time.

On the right side of the table, you can see the players’ serve attempts in a particular set and the number and percentage of rallies won after their serve.

Here, we can see that the most balls were served by player #9, and the percentage of points won after his serve was second highest in the team. The same number of rallies were won after the serve of player #11 (Sliwka A.), but because he had fewer attempts, his %PS was higher (67%).

The table on the right will show how many times the player served the ball in a row. You can see who is/was getting the most balls to serve during the game.

Here, we can see that player #9 (Leon) had the most series of serves in this game, and the longest one contained five serves. The longest series of serves had the player #11 (Sliwka), who served five times in a row, but it was his only series in the entire game. Another effective serving player was also #30 (Boladz B.), who had two series in which he served three times in a row, and #21 (Fornal T.), who served four times in a series.

Important formulas in this spreadsheet:

=IF(L6=$B11,VSMAP(VSPLAYS(VSCELL("*S;S1;(1)",$C$5),VSRANGE(L5&":"&L5)),"effect"),"")

This formula gives the effect of the serve in the first set, in the game specified in cell C5 within a particular range if we have the same values in the cells L6 and B11.

=IF(VSCOUNT(VSCELL("(1)S;S1;(2)",$A$9,$C$5))=0,"",VSCOUNT(VSCELL("(1)S;S1;(2)",$A$9,$C$5)))

This formula counts the number of the player's serves from cell A9, in the first set, in the game specified in cell C5 and shows if it was different than 0.

=IF($A$8="","",IF(VSCOUNT(VSCELL("(1)S;WR-0;S1;(2)",$A$8,$D$5))=0,"",VSCOUNT(VSCELL("(1)S;WR-0;S1;(2)",$A$8,$D$5)),$D$5))

This formula gives the number of rallies won after the player's serve from cell A8, in the first set, in the game specified in cell D5.