Football Statistics Yardage

[Hoolix21]

I have a Workbook with several spreadsheets in it that I use to stat our games. With the help of someone on this board I was able to create this; sorry I don't recall whom it was, but it was whomever listed the sheets with SUMPRODUCT for football. Now I am trying to add a little convience to this spreadsheet so that it auto fills in the yard line that the ball is on from the result of the play. For instance We have the ball on our OWN 35 and complete a 16 yard pass. Now the ball would lie on the OPP 49, though excel would say 35 + 16 = 51.

 

[Marc]

Hey Sportsfan-

Assuming the line of scrimmage is housed in cell A2 and the yardage play in B2:

How about either:
=IF(SUM(A2:B2)>50,SUM(A2:B2)-50,SUM(A2:B2))
=IF(SUM(A2:B2)>50,"OPP " & SUM(A2:B2)-50,"HOM " & SUM(A2:B2))

The second one would add either OPP or HOM to indicate who's side of the field the ball is on.

Cheers, Marc

 

[Hoolix21]

Just from looking at your reply and not actually being at my desk to where I could test this; I don't think that it would work because if I was on the 35 and gained 16 yards that would put me on the "51" if I subtracted 50 it would say I was on the 1 instead of the 49.

 

[Joe4]

You are correct. Something like this should work though:

=IF(A1+B1>50,100-(A1+B1),A1+B1)

Or this if you like to add the detail Marc suggested.

=IF(A1+B1>50,"OPP " & (100-(A1+B1)),"OWN " &(A1+B1))