Passing play by play

This sheet contains a play-by-play analysis of the team’s reception. 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 have the number of the set and the score. See the example table below: SET 1 (26:24).

For each set, there is a table that shows the player and his passing attempts chronologically. It allows you to see how many balls the player passed in a row and what the results were. This shows the efficiency of the passing players over time and is useful for tactics preparation and choosing wisely the player who should be the target of the serve. 

In this example, we can see that the opponent’s team changed their tactics during the set. Initially, they served many times in a row to player #11, and then most balls were passed by player #10.  We can also see from the results in the table that player #11 had a better quality of reception, which probably inspired the change in service tactics.

On the right side of the table, you can see the player’s passing attempts in a particular set, the percentage of positive reception, and the number of passing errors.

Here, we can see that players #10 and #11 passed the same number of balls, yet player #11 had a far better percentage of positive passing.

The table on the right will show how many times the player passed the ball in the row. You can see who is/was the main target of the serving team and how many passing series they had.

Here we can see that players #10 and #11 had the longest series of passes since they passed the ball four times in a row, while, for example, libero #22 didn’t have any series during the whole game. It allows you to see who the main target of the opponent team was.

Important formulas in this spreadsheet:

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

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

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

This formula counts the number of the receptions in the first set, in the game specified in cell C5 and shows it if it was different than 0.

=IF(VSCOUNT(VSCELL("(1)R;S1;(2)",$A$9,$C$5))=0,"",VSPER(VSOR(VSCELL("(1)R#;S1;(2)",$A$9,$C$5),VSCELL("(1)R+;S1;(2)",$A$9,$C$5))))

This formula gives the percentage of positive reception in the first set, in the game specified in cell C5, and shows the number of these receptions was different than 0.

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

This formula counts the number of reception errors in the first set, in the game specified in cell C5, and shows them if the number of these receptions was different than 0.