Oguard62.files.wordpress.com



One of the keys for this was in determining how frequently certain yardages would be the result on pass plays since those yardage results are the basis of the chart itself.? As I mentioned I have an Excel helper of my own creation that I use to play the games.? From that spreadsheet I was able to pull the yardage results from the Passing Boards.? I did not use the Long Pass results because I didn’t think it would have much impact on this creation due to so few yardage results being in the range of -2 to 40 yards that I’m using for this. ?I also ignored FD+ results as those yardages will vary from play to play.? ?On the attached spreadsheet the frequency of each yardage result is on the top of the spreadsheet.? So as an example, the most frequent yardage result on the Short Pass boards (def 15 to off 31) is 6 yards which occurs 19 times. ?Which you will see in cell D13. ??In column F I calculated the weighted frequency of each yardage result on the Short Pass boards.? ?The weighting is based on how often each field position distance is used.? I got these frequencies from an old APBA Journal.?? Row 2 has those percentages listed.? Those percentages can be changed and the results throughout the spreadsheet will be recalculated.I did the same exercise with the Medium and Screen pass yardage results.? Those are also in the spreadsheet.? So, columns G, M and R have the percentages for each yardage result for each type of pass play.? I then weighted these individual pass play percentages by the recommended Short/Medium split.? I used 50% for Short, 40% for Medium and 10% for screen.? Using those percentages Column U then calculates the final frequency percentage for each yardage results. ?The most frequent yardage result by my calculations is 6 yards at 7.19% as you can see in Column U.One big assumption here is that the yardage results will be random.? Obviously, the passing columns of the QB’s will come into play.? This process assumes an even mix of QB card results from 3-20.? I could probably incorporate the QB results into this calculation but I don’t really think it will change things all that much.? I didn’t gather any data around how frequently the QB card results from 3-20 occur. ??Also, the frequency that each index is used might impact things slightly.? But all I am looking at is completions and the yardage that results from those completions.?Have your eyes glazed over yet?Now I’ll move to the bottom of this spreadsheet.? ?It’s a bit less complicated.? Beginning at Row 52 on the left side of the column is where the YPC chart was created.? Each YPC rating has a column, the -5 rating is in Column B.? There is a corresponding column for the -5 rating in column S.? Column S is calculated as changes are made in Column B.? It’s simply a calculation of the difference between the value entered in Column B when compared to the Yardage result for that row.? For example, Cell B54 is 30.? It’s in the 38-yard row. ?Therefore, cell S54 is calculated as -8.?? Then that -8 value in S54 is multiplied by the value in cell AK54.? The values in Column AK are the frequency percentages from the top of the sheet. ?In this case the percentage of time the 38-yard result is found on the passing charts .049%. This gives each yardage result (38 yards in this case) it’s proper weight for this YPC rating. ?Those calculations are done for each YPC rating and Yardage result.? Column AK is then totaled at the bottom and divided by 100 to give the final average yardage for the -5 YPC rating.? In this case it’s -5.1 yards. ??The calculated values for each YPC rating are in row 96.? I had these values all matching exactly so the -5 YPC rating had 5.0 value instead of the 5.1 you see here. But in putting this explanation together I caught a couple of minor mistakes.? I’ll probably fix those before finalizing things.I hope this makes sense.? Feel free to ask any questions. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download