Football play-by-play

[MikeJS253]

I'm trying to create a basic spreadsheet to track the play-by-play of a football game. There are sheets for offense and defense. For the sake of this question, I'll deal only with the sheet for offense. This sheet contains columns for drive number (E), down (F), distance (G), yard line (H) and yards gained (L).

Formula in "Down" column (F) reads:

=IF(L2="","",IF(L2>=G2,"1",F2+1))

This works as long as yards gained (L) does not exceed distance (G) on a first-down play. For example, if there is an 11-yard gain on first-and-10, the next line reads second down and minus-1 yard to go (should read first-and-10 again). However, if there is a gain of, say, 6 yards on the same first-and-10 play, the next line correctly reads second down and 4 yards to go. Suggestions?

Also, I'm trying to set it up so that falling short on fourth down does not produce a fifth down; instead I would like to have it roll over to first down while increasing by one the information in column E, which signifies drive number.

As for yard line, I was hoping to use -30 to signify a team being at its own 30-yard line, and 30 to signify being at the other team's 30-yard line. I would manually input the yard line on the first line (H2). The yard line would then change by the appropriate amount based on yards gained (or lost) in column L.

Excel is a great program, but I'm new to writing formulas and have been banging my head against a wall on these. Any help greatly appreciated.

 

[jonesy241]

Interesting - Can you tell me what you are inputing?
If you can post a html version of your sheet it would probably be very helpful. Are you entering the yards gained per down? and then everything calcs off of that?

 

[MikeJS253]

I'm inputting yardage numbers. The columns for down and distance change accordingly. I'd also like to make the yard-line information change accordingly.

How would I post an HTML version here?

 

[MikeJS253]

Fixed one part of it

I was able to fix the down-and-distance formula.

It now reads:

=IF(L2="","",IF(L2>(G2-1),"1",F2+1))

instead of


=IF(L2="","",IF(L2>=G2,"1",F2+1))

Not sure why I had to make this change to ensure that a 10-plus-yard gain on first-and-10 would produce a new first down, but it does work. Must be something about >= that I do not understand.

My next challenge is to figure out a way to make the yard-line update automatically. This is a tough one because if Team A gains 10 yards from its own 45-yard-line, the next line of scrimmage is at the other 45, not the 55. I'm sure we can figure out something. Any help appreciated.