Passing by rotation

In this spreadsheet, you can find the analysis of passing by rotation. It is prepared to be used on the bench during the game. 

How does it work?

In the table on the top of the sheet, you can find the number of passing attempts and ratings for each player. It helps analyze how the player passed in a particular rotation, where he/she had troubles, and how that can be used during the game.

Below, you can see play-by-play passing in different rotations. You can find the passing player, pass rating, passing zone, and passing effect there. These plays can be filtered by passing player, zone, and effect.

In this example, you can see that the players were passing well in rotation 4, and that the main target was player #12, who was struggling a bit. However, in rotation 2, all players had difficulties passing the ball, which was probably the result of the excellent serving player of the other team. The most balls were served into zones 5 and 6 in this rotation. 

Important Formulas in this spreadsheet:

=IF(VSCOUNT(VSCELL("(1)R",$C3))>0,VSCOUNT(VSCELL("(1)R",$C3)),"")

This formula shows the number of passing attempts of player from cell C3 if this number is bigger than 0. If this player didn’t pass the ball, the cell remain empty.

=ROUND( ( (VSCOUNT(VSCELL("(1)R#",$C3)) * 4 ) + (VSCOUNT(VSCELL("(1)R+",$C3)) * 3 )  + (VSCOUNT(VSCELL("(1)R!",$C3)) * 2) + (VSCOUNT(VSCELL("(1)R-",$C3)) * 1 ) + (VSCOUNT(VSCELL("(1)R/",$C3)) * 0.5 ) ) / VSCOUNT(VSCELL("(1)R",$C3)),2)

This formula counts the average of the receptions of player from cell C3, by counting the number of perfect receptions and multiplying it by 4 than add the number of good receptions multiplied by 3, then the added number of neutral reception multiplied by 2, the number of negative receptions multiplied by 1, and number of overpasses multiplied by 0.5. This sum is divided by the number of all player receptions from cell C3. The result is rounded to two decimal places.

=VSMAP(VSPLAYS("*R;*Z1",VSRANGE("1:1")),"player")

This formula gives the player who received the first ball in the game in rotation 1.

=VSMAP(VSPLAYS(VSCELL("(1)R(2)0;*Z1",$E$11,$E$12),VSRANGE($E16&":"&$E16)),"effect")

This formula gives the effect of the reception in rotation 1, of the player from cell E11, in the zone from cell E12, while the play was in range marked in cell E16.

=VSMAP(VSPLAYS(VSCELL("(1)R(2);*Z1",$E$11,$E$13),VSRANGE($E16&":"&$E16)),"startZone")

This formula gives the starting zone of the reception in rotation 1, of the player from cell E11, with effect from cell E13, while the play was in range marked in cell E16.