Scouting using Excel, please help

[Cade123]

Hello,

My team is attempting to use spreadsheets (Microsoft Excel) for scouting this year. We would like to be able to submit data chronologically as we receive it at competition (as in, data from match one followed by data from match two and so on). Obviously, we don't have access to our competition match schedules so we need to be able to input that on the fly. My question is, how do you take data entered and sort it by team if we don't know what teams will end up in each cell?

If other information is required to answer this please let me know.

Thanks.

[Cothron Theiss]

How you set up your spreadsheet is dependent on how good you are with Excel and what interface your scouters want to work with (and how much time you have). I'd suggest using online tutorials for learning more about Excel as your first resource. Another great resource is to read through all the threads Rachel Lim has started. Many of them are Excel or Google spreadsheets, and the solutions she has been able to implement via spreadsheet are remarkable.

[Darre_Bonfire]

I have not used Microsoft Excel as a Scouting tool, but I have heard that it is a pain to use and looking at it from a Scouting perspective I can see why that would be so. This year my team is starting to use a program called Tableau for our Scouting organization. Using an app we created to gather the data then putting it on the program to organize and quickly use. It is free for FRC teams as long as you get the code, mentors should have access to it I believe, I know my Scouting mentor did.

https://firstfrc.blob.core.windows.n...amUpdate03.pdf

Here is the link, and hopefully your mentor has access to the code to get it for free, there are actual tutorials on how to use this application, and you wont find very many tutorials on Excel scouting.

Good luck.

[DoubleD-STL]

We have used Excel for our scouting the past few years. Try using the Vlookup function. This will allow you to find data on another sheet without having to know exactly where it is.

During the matches, the students record the data on our score sheets. Every 15 matches or so, I enter the data from the score sheets into the master file.

DD

[throwaway]

A very simple solution would be to use filters. You would enter data row by row and when you want to look at the data for a team use a filter for just that team number.

[Mike Schreiber]

If you're opposed to using VBA or not super familiar with excel functions, I'd recommend a lookup column that tells you if a certain team number is in that row. Try iferror(match(cell,array,0),""). PM me and I can walk you through some fancier excel stuff. We scouted with excel in '14 and I was happy with the results, I can send you that sheet.

[Eric Scheuing]

You can setup a table in Excel that will allow you to sort and filter on a specific column. For example, if your first column is team number, you can filter out all data except the selected team number.

The best part is it's extremely simple to use. All you need to do is press CTRL+T and select the area you want to tabulate. Tables are smart too, and allow you to input data on the next row or column and it will expand the table area to accomodate.

You could start getting into pivot tables for in-depth analysis, but I recommend staying away from those if you aren't too familiar with Excel.

[Mullen]

I have used Excel for scouting from 2012-2016, with fairly good results (made me pick your team twice at worlds in that time frame). As Mike mentioned, VBAs/Macros can make life a lot easier, though there are ways around it.

I used Macro's for submitting the data, but pulling it all back up for a team summary, rankings, etc. can be done with vlookups.

I'm taking the year off, so I'm not making a file this year, but if you have any questions or want some advice send me a message.

-Pat

[KrazyCarl92]

I'll provide an example I am familiar with...

Team 20 has used excel in some form for a number of years to conduct scouting. 2012 was my first year leading that effort, and in each year thereafter in which we used excel for scouting, we set our sights on improving the system to provide more and better information.

I've attached 2 files:
1. Our scouting spreadsheet from the 2013 Connecticut Regional
2. Our paper scouting sheet user/training guide for the 2013 Connecticut Regional (first page laminated and attached to each scout's clipboard!)

In the "Ultimate Ascent Raw Data" tab, the numbers from the right hand margin of the paper scouting sheets are entered in order from left to right. This was designed to streamline the data entry process and make the data entry person's job as easy as possible. This way their job became: <team#> [tab] <match#> [tab] <first letter of color> [tab] <next number down> [tab]... until they reached the end of a row. Hit enter, then proceed to the next paper scouting sheet. 6 sheets per match (one for each robot).

The "Stats" tab in the spreadsheet is where the magic happens. All of the information in the raw data tab is turned into useful summary information. I.e.: average auto points per match, shot accuracy, standard deviation of match scores. To understand how each of these calculations is performed, take a look at the formulas in the cells. Excel functions like "COUNTIF", "SUMIF", and "AVERAGEIF" are your friend here. The way this spreadsheet is set up, these commands search for team numbers in the first column of the raw data sheet that match the corresponding value in the first cell of the row in the "Stats" tab. It then averages, counts, or sums all of the data in the correct rows in the "Raw Data" tab. If the headings for each column are confusing (heck, I don't even remember what IA% or DOAL mean anymore...), try clicking on the cell. All abbreviations have been set up through the cell data validation --> input message to state the meaning of the abbreviation.

You can also filter the "Ultimate Ascent Raw Data Tab" by team number to take a look at an individual team's performance in each of their matches and look at trends. Or sort by match score, etc.

The "Team Lookup" tab allows the user to set up a combination of 6 teams for a match. This is an incredibly useful way of viewing the information when planning for a match or discussing alliance selections.

A few key notes:
-IF you are using paper then entering data into excel, it is crucial to make the data entry person's job as easy and straightforward as possible. This means streamlining your process and limiting the data you're collecting to only what you plan to use. The fewer questions the data enterer has to ask, the better.
-We used data validation extensively to mitigate issues like the wrong team number being entered into a row in the raw data tab. Clearly this would result in missed data if someone accidentally entered "<19> [tab]" when they intended to enter "<195> [tab]", so our spreadsheet kicks it back to the user and prompts them to correct the error. We also used this so that if the user lost track of where they were, they could be alerted when they tried to enter that a robot scored 10 pyramid goals (there was a max of 6), when they meant to enter that the robot had MISSED 10 shots in teleop.

Future improvements included the addition of picture lookup, in which a picture of the robot would appear based on the team# entered in the "Team Lookup" tab, and multiple data entry so that one laptop would enter the data for the blue alliance, while another entered the data for the red alliance. I included our 2013 spreadsheet because it is simpler to follow than multiple spreadsheets with outside references, and strictly takes advantage of basic functions already available within Excel.

I have worked with and seen a number of other scouting systems outside of excel, and most often find that the data presentation is lacking compared to what we have here. That's not to say that these electronic scouting systems don't have their merits, but come on...if you can make a table that summarizes all pertinent information for all 6 teams in a match on a single screen in Excel, don't make an electronic scouting system that cannot meet that same standard.

The biggest problem with paper + excel scouting is efficiency. If you spend the time writing down the data, then later entering it into the computer, you're duplicating efforts. Electronic scouting can be nice if it mitigates this, and can also be used to provide additional useful information, such as heat maps of shooting locations, etc.

If you have any questions on this example, feel free to post here or send me a PM and I'd be happy to share more.

[Skyehawk]

At the risk of derailing this thread from excel talk...

Excel is better suited as a visual data analysis tool, not so much as a data input software. That being said it has played key roles in scouting for us.

If you have Microsoft Office products available have you considered using MS Access? The data input forms may be right up your alley. It is also a very powerful visual database, well worth the time to familiarize yourself with at a minimum.

Best of luck,
Skye Leake

[BASE]

If you still have questions about an Excel Scouting system, please feel free to pm me.

Team Dave has been using exclusively Excel for quantitative scouting for the last 4 seasons.

[Cade123]

Thank you all for your quick help. I was able to solve the problem I was running into. If I run into more problems I will definitely return here for help.

Good luck to everyone with their robots and scouting this year!

[Captain_Kirch]

Sumif
Sumifs
Vlookup
Averageif
Averageifs
Sumproduct

These functions were my bread and butter for spreadsheet design. Excel is a great tool for data input, sorting, computation, and visualization. I would recomend tableau for visualization though.

You dont need macros, vba's or anything else fancy to harness the power of excel. Just get in there and make something.