As an extension of an earlier post, I’ve created a utility to help you evaluate the effectiveness of your gameplan (and to compare the effectiveness of different versions of it). You can access the worksheet here. I’d suggest downloading an excel version of this if you want to come back later to edit, as the relevant portions can be edited by anyone.
Follow the instructions to input (a) the soft counter % from your gameplan, (b) which plays you have hard countered, and (c) the play call frequency from your play history (this last step is if desired; if you leave the column blank, it will just use play call frequencies I entered from my own history).
From this, the worksheet will tell you the:
Overall % of plays countered
% of 3rd and 4th down plays countered
Additional % failed 3rd and 4th down conversions uniquely produced by your gameplan.
Not going to have much of an impact on the data since it's not commonly used, but HB Cutback is missing from the list of plays in your spreadsheet. Just FYI. Very interesting tool. Most appreciate the work that went into it.
Eek - ok, hopefully this is fixed now. I take it this was your soft counter %? Give it a try again, and let me know if you're still having troubles. I implemented pretty tight controls to prevent trolls from messing with the formulae, but might have caught some portions I shouldn't have.
Also, I forgot to mention that I have other tabs that give a complete listing of gameplan cards as well as a listing of play frequencies. Note that in the listing of gameplan cards, you can filter out or in specific plays and/or what you want as a soft counter percentage. So, for example, if you want to see all the cards that have a soft counter for running plays and a hard counter for Str HB Blast, you can filter appropriately. However, you'd need to do 2 different iterations of this because sometimes Str HB Blast appears as "Play 1" and sometimes as "Play 2." There are some hidden column throughout, too - feel free to check them out, though they're mostly used in calculations. Please put them to being hidden as you found them when you're done.