I created an Excel workbook capable of automatically processing data using VBA. The files are available on Github, as well as a guide on how to expand the spreadsheet to keep track of some variables that are not already on it. The reason this was made is because our team cannot rely on internet or data, so we needed an easier method of offline scouting.
There are two premade spreadsheets, one for the Bethesda MD competition and another for the Edgewater MD competition. Good luck to everyone attending!
On the spreadsheet labeled “Data Input” the user inputs a team’s performance during a single match as one row. The user continues entering rows into the Data Input spreadsheet, and all the data points are averaged out for each team into the “Processed Data” spreadsheet. Right now, there are 13 variables kept track of by the workbook. These are:
Autonomous Mode Bottom Power Cells
Autonomous Mode Outer Power Cells
Autonomous Mode Inner Power Cells
Autonomous Mode Percentage Initiation Line Crossed
Tele-Op Bottom Power Cells
Tele-Op Outer Power Cells
Tele-Op Inner Power Cells
Tele-Op Percentage of times the team completed rotation control
Tele-Op Percentage of times the team completed position control
Endgame Percentage of times team completed climb
Endgame Percentage of times the alliance generator switch was balanced
Win Percentage
Games Played
A guide to adding custom variables and processing them through the code is available on the Github
On the macro enabled sheet, quite literally nothing happens when I press the button. I entered some fake data which should be valid-ish (assuming I’m understanding each header correctly). What Macro is supposed to execute?
On the macro enabled sheet, I also tried to manually execute the Macros (not using the button) and nothing happened. EDIT: I discovered that I need to manually enter corresponding team numbers on each sheet. If I were you, I’d build it in to your code to do that automatically.
What is the purpose of the non-macro version? If it isn’t doing anything with the data, why would I use it? (This is not intended in a snarky way, I am actually trying to understand the use case)
Is there anything being calculated using macros that cannot be calculated with simple formulas? When using formulas rather than VBA, it will generally load much faster, work on a variety of additional platforms, and be easier to debug and modify for outside users. Since I can’t really see what’s actually happening (based on questions 1 and 2 above), I can’t say for certain that it’s not doing anything that requires visual basic, though I’m skeptical.
While I am not new to programming, I am very new to VBA, which next to no one on the team has experience in. While I may integrate this feature in the future, for now I have other priorities with the team, and just needed a quick script to process data with the spreadsheets the team already had, which included team numbers already manually entered.
The macro enabled spreadsheets are of the .xlsm file type, which has the ability to mess with files on the computer. Because of this, it is possible that some computers, say school laptops, would not allow users to download .xlsm files as a whole, so I included the .xlsx files separately along with code that can be copypasted into it. Even if this is just an excuse, including the .xlsx sheets (which I already had) into the repo was easy and has no negative consequences.
In fact, someone on the team did create a spreadsheet which uses formulas to do the same thing, after we realized that a google sheets script prototype would not be viable in a competition without a reliable internet connection. My issue with it was that their version required the Data Input spreadsheet to be sorted by team number, while I wanted to be able to quickly enter data into the sheet, without having to sort by team number often.
I’m mostly clear on everything except for this part. A quick =SORT(UNIQUE(RANGE)) in your team number column and a =FILTER() or =VLOOKUP() or =HLOOKUP() should be able to do exactly what you need.
Would it be possible to add “cells obtained” during autonomous period? In addition to just averages It would be really cool if there was a mechanism that showed trends overtime as well as highlighted outliers(bad match lost coms etc) rather than only having the numerical data. We have always used paper in the passed because of this reason, but if it could be easily implemented we would love to give your spreadsheet a try!