B1G Alumni - Skilled Offensive Player Performance in the NFL

Follow this link to interact with the data: https://1drv.ms/x/s!AjLEpw37FeB1gawOW6TWpHSq6TuqRQ?e=poEg7t

NOTE: If you do not have Microsoft 365, functionality and viewing capability will be limited

Data/Approach At the risk of writing out descriptions of every complex formula and concept that is included, this entire description is intentionally brief. I am a huge fan of the National Football League, and I am a B1G Alum. Like many Excel Mavens out there, I have always employed the same concepts to manipulate data, only to learn new tricks on occasion. And like many other Excel Mavens, I know that there's always more to learn, just a question of WHEN. <------- Not sure if that should be a period or question mark but will keep this moving

I finally took the immersion opportunity and learned about a bunch of concepts like

• Sum-ifs with Data Validation - Sum-ifs probably aren't going to be new to any veteran Excel folks, but this was my first time combining with Data Validation. This is probably the first step of fake automation in Excel =). If the user selects "B1G Conference", then return only "B1G Conference" numbers. It gets more fun when applying that concept to charts and color!
• Index/Match and Rank.EQ - Probably the most challenging concept here, but piece of cake if you use it frequently. In the 'Index Match Filled Map' tab there is a chart for Top 10 Overall. Index is limited by itself, as is Match, but combining them allows for something like a 2 dimensional coordinate system. In that chart, you can select the year (1999-2913) you want to review, so Top 10 performers won't necessarily be the same. Rank.EQ allows me to define who performed the in a given season, and it the numbers 1-10 serve as the INDEX. Index/Match allows us to return all the appropriate "coordinates" for whoever is #1 that year. The 3 concepts help us sort the Top 10 and their information correctly, regardless of what season we are looking at.
• TopN Calculations - in the Top N tab, the user can select the year they want to view, and who the Top players from that season were at each of the positions. TopN isn't actually a formula, rather the location of the user input. In this case, you can enter a number betweeen 1 and 50 in the cell location of F3. It is important because I would prefer to not restrict your analysis when I provide my dashboard. I want YOU to be able to select what you're interested in.
• Fixed Arrays with Sort, Filter and Large - several array functions (like Index Match) are best when used in combination with others. In the Top N tab for instance, the formula incorporates LARGE which will return the highest of a given range; Filter which returns based on an argument - position in this case; and SORT which returns the desired order. The chart includes 4 different positions and the user can select the season through the Data Validation dropdown. The results are sorted in a way that would not otherwise be possible. That matters to me because I think most people reviewing numbers would like to see them sorted, so best to do that in a more "automated" fashion.
• Highlighting dynamic bar chart series with deliberate/strategic color designation - color just makes it easier to point the end users to where their attention should be, as does using Titles to tell the story. It also helps from a real estate aspect because now you don't need to use legends or any other designation to define the data (Data Validation, Form Controls tabs)
• Form controls - this really gives an app like feel (Form Controls) and more importantly helps keep the real estate usage to a minimum

These methods can allow for complex and deep dive analysis without having to turn to R, SQL, Tableau or Power BI. It's just a A LOT of work. However, you will have a brand new appreciation for those tools, and you would have essentially gone under the hood without actually going under the hood in those environments.

Observations & Reflections The main challenge here, and not just in Excel, rather all platforms, is Frequency. I would recommend for anyone doing any of this for the first time, chart your landmarks. Take notes on it EVEN THE FIRST TIME THROUGH. Ideally you will have data from other projects that you can leverage to re-create your work, but even then you'd want to have your notes handy so as to avoid that feeling of starting over with your original study.

As for this particular data set and topic, the one thing that could offer real insight on how "dominant" any conference might be at the next level is the element of TIME a particular coach or particular group of coaches are in place at their institution(s). And to that end, the qualitative factor of WHO that particular coach or group of coaches are could also shed light the same way a mergers & acquisitions deal, or a commercial credit analysis would consider leadership teams. And since this data is lacking in that regard, I can not in good faith conclude that the B1G Conference is not superior. =)