NCAA Program with Statistics Based Question

Hey excelforum.com,

I coach Division 1 Volleyball and our staff was interested in using Excel for a training statistic. Admittedly, we are not particularly Excel savvy outside of elementary formulas. Thus, we apologize for any ignorance and greatly appreciate any help.

Currently, we are recording the miles per hour of our player's serves and charting their serving errors simultaneously. One statistic we really value is average MPH of a player's serve multiplied by the player's percentage of serves made. We are currently recording and equating most of this by hand and thought that there had to be a simpler way via excel.

Here is what we are looking for:
- a column with the player's name
- a column with the number of serving attempts
- a column with the number of serving errors
- a column with the percentage of serves made
- a column with the average serve speed
- a column with the average serve speed/percentage of serves made coefficient
- a column with the highest recorded serve speed (optional)

As of right now, we are having to manually calculate average serve speed and percentage of serves made each day for each player and then putting them into Excel for the program to perform simple math.

What we would like is to have 1/2 data entry cell(s) that would update the entire worksheet dynamically. For example, having a cell or two that would allow us to type "Smith" (for player's name) and "E42" for a 42 mph error and the worksheet would add and attempt, error for Smith while simultaneously updating her averages and percentages.

First, is any part of this even possible? Second, is it feasible for a group of people with limited Excel experience? Thanks in advance for any and all advice and again, we apologize for our naivete.

Coach KC

Hi.

Yes this is possible, but would involve a lot of hidden cells in its simplest form. What your best bet may be is to have someone develop you a userform that you can enter all of that data into each time a player serves, and then all of the information is stored and calculated where it is then displayed on a worksheet.

This could be done with relative ease, however I suspect you are after something a little simpler that you could do yourself...?

Let me know, and I'll see if I can help you out with the hidden cells idea if that doesn't bother you at all

Ajryan,

Thanks a bunch for the prompt response. It's much appreciated.

We actually looked into the userform idea and our biggest concern was time for entry. We have drills where players are serving every 3 to 5 seconds and it seems unlikely that we could continually type in all the necessary info accurately over time. Does that sound right?

If it isn't too much trouble, we'd love to investigate the hidden cell idea. Admittedly, we would need to have our hand held through the process, but we are certainly game for at least trying. Any and all help, thus far and in the future, is really valuable. Thanks again.

Coach

You're welcome.

I actually played around with it a while, and decided to just build you something that you can play around with and let me know (I'm just not a great teacher sorry, even though I have the knowledge). This does not include any hidden cells, but it does have some code that runs in the background when you press either of the buttons on the spreadsheet.

Simply press the Submit button to update the statistics in the table, and press the Clear Form button to start a new day and wipe all statistics and values off the board.

The "Name" data entry point should be self-explanatory, as well as it having it's own drop-down list anyway.
As for the "Serve Code" data entry point, either type in the speed of the serve for a successful serve, or prefix with an "E" or an "e" for an error (as you stated in an example in your original post).

This will also work if you wish to add more players (up to 49 players are allowed in this table).

Please also feel free to make it look pretty, as this is not my area of expertise.

I hope this is what you are looking for

ajryan -

This is awesome! So much better than anything we had come up with this far. We have 15 players on our roster. Is it as simple as pulling the formulas down for each cell and changing the name or do we need to take a different route? Also, would it be possible to include a row for the team statistics involving all serves? If we were interested in tracking these statistics over various days, using each days results as a subset (perhaps as individual sheets in the same workbook that update the master), would we be able to do that fairly easily?

Just some thoughts we had after looking at what you put together. Needless to say, it's far more than we were hoping for. Unfortunately, the NCAA is going socialist and anything outside of a simple thanks gets us thrown in jail.

KC

Hi Coach.

To add players, simply type more names into the names column, and the formulas should all pick up the extra players by themselves (I hope! - you better let me know if this doesn't work).

Certainly, a row could be added for team statistics too. I will edit the spreadsheet that I uploaded a bit later today and re-upload it with this row.

For tracking across multiple days, perhaps the easiest option would be for me to add another button that will copy the results at the end of each day into another worksheet in the workbook, and then will update another "master" worksheet as well, which would consolidate all results from every worksheet for individual players AND the overall team summary. Then another button to start a new week perhaps (or however many days you wish to track on the master), which will erase all previously saved days and reset the master for a new week. Does this sound like the sort of thing you are looking for?

AJRyan88

Here is the spreadsheet updated with team summary information. I will look at the other potential changes once you give me a bit more clarity around what it is that you want specifically (see my previous post).

Thanks

This is great. Although, I think I might be missing where individual sessions would be tracked over time. Likely an oversight on my part but I can't seem to find it.

Another idea a colleague had would be to not track the speed of of errors to promote the idea that serving out is detrimental, regardless of speed (high level stuff, I know). It seems like perhaps coding each error as simply "E" and not including a speed would be the easiest way. Thoughts?

KC

Hi Coach,

No I haven't put in the individual session tracking yet, as I was waiting to hear from you regarding my thoughts in my second last post.

I can definitely make the program do no calculations for errors, just let me know if this is what you want and I'll implement that and upload a new spreadsheet.

The individual session tracking would be huge as we could chart serving over weeks/months/seasons. For the error issue, it seems like the Average MPH would have to change to only take the average of serves in play versus all serves because if it included the errors in the amount that the average was pulled from with no speed, the average would be unfairly pulled down. Sound right?

Hi Coach,

Try this attachment.

Avg is only calculated on successful serves, and you only need to enter "E" or "e" for an error now.

You can also save each session using the "Save Session" button, and this will automatically update the master for you too.

When you want to start a new master, just click on the "Reset Master" button on the master spreadsheet.

As always, let me know

Bingo! This is it! Thanks so much. We'll continue to see if we can tinker with it but this was so much more than we were hoping to get. I'll continue to check in with any questions we may have but until then, your help has been immeasurable.

KC

You're extremely welcome, I'm always happy to help!

By the way, it might be worth mentioning that when you reset the master, all of the saved sessions are not deleted automatically, so I can give you 2 options here:

1. You can manually delete all of the respective worksheets
2. I can make another copy of your workbook where these worksheets are deleted upon a master reset.

The only reason this would be problematic is because if these sheets aren't deleted, they will be taken into account next time you save a session and the master is recalculated.

And please don't forget to click on the star beneath my post to say thanks, and when you are satisfied with the answer mark this thread as solved (you will still be able to ask me questions once the thread is marked as solved).

Enjoy