Determining Football Success...

  • Greetings and thanks in advance.
    I have written a workbook to chart American football. Each row represents one play in the game. I have 6 columns in which I input data (i.e. offensive formation, pass route, defensive alignment, etc.). There are an addition 215 columns that have formulas that "read" those 6 data columns. For example, if I put "Split Left" into the Formation Column (Column E), then column DA (titled "Split") will mark a 1 because it read column E in the same row. Also, column DB (titled "Left") will also record a 1 because it read "Left" in column E.

    I also have a formula in column Z that determines whether the play in that row was successful. Thus Column Z will record either a 1 for a successful play or 0 for an unsuccful play.

    At the end of the game it's obviously fairly easy to have a report that might indicate something like the following: "When the offense was in Split formation, they were successful on 62% of those plays compared to Pro Formation in which they were successful on just 38% of those plays.

    Here's my stumbling block: What I would like to be able to do is have Excel create a different report such as: "The factor(s) that lead to the highest rate of success (according to Column Z) were __________" For example, perhaps the program would report that, based on reading column GY (which records whether the offense ran to the wide side of the field or the short side of the field), the factor that had the highest success rate was when the offense ran to the wide side-- 91% of the time when they ran wide they were successful.

    Even better would be if the program could report on two or more factors (each factor is a column). For example, perhaps Excel could determine that when the offense ran to the wide side of the field AND they were in Split formation, they were successful 96% of the time.

    I hope that this message is lengthy enough to be clear while not being tedious.
    Also, should I be using Microsoft Access for this task? In addition to reports for individual games, I would like to have reports that combine each game for running season totals.
    Thank you very much for the help.

  • Re: Determining Football Success...


    I think the report can be done easily enough..

    Can you attach a copy of the workbook and maybe ill fill in the formulae for youi



  • Re: Determining Football Success...

    Always Learning-

    Welcome to the Forum!!!

    Bear in mind that you may need to define what you mean by success. Some plays are designed to gain short yardage (QB sneak, for example), while a sweep or reverse is generally expected to gain more yardage. Is a play that is designed to gain 10 yards a success if it gains 9?, if it gains 15 but was the result of a non standard set of circumstances (the QB dropped the ball but it bounced into the hands of the fullback who ran for 15 when the play was designed for the halfback)? A fake field goal that results in a first and goal versus a fake field goal that results in a touchdown?

    With 215 columns of formulas that will "analyze" the six columns of entered data, you may well have taken these factors into consideration. Please don't take my comments as a knock on your project. It sounds very intriguing and I wish you well in completing it.


  • Re: Determining Football Success...

    Thank you for your reply, I will forward a copy because I am intrigued that you may be able to have a solution.

    Thank you too for your reply. I have a formula that takes into account the down, distance to go, the gain of the play and a couple other factors.

    The toughest circumstance I have encountered is when the offense has third and twenty from their own 2 yardline. If the offense gains 19 yards to bring up fourth and 1 and thus requiring a punt, is the play successful? You could argue no because the first down was not reached. You could argue yes because the offense has given the punter room to punt and 19 yards of field position has been acquired to help the defense.

    Thank you for your input. I see that you're an accomplished Excel user. If you have any thoughts on how to produce the report I've described, I would be extremely delighted.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!