I am trying to use Excel as a stat keeper for football. I want the down and
distance cells to change automatically when i enter a distance gained after
each play. I used the following formulas for the down and distance cells
respectively:

=IF(I18=D18,"1",C18+1)
=IF(I18=D18,"10",D18-I18)

Where I18 is where the yardage gained for each play is entered, D18 is the
yards to gain, and C18 is the current down of the play.

For the down cell i want a "1" to be displayed if the yards gained is
greater than or equal to the yards to gain. For the yards to gain cell i
want "10" to be displayed if they yards gained is greater than or equal to
the yards to gain and if not then display yards to gain(D18) minus yards
gained(I18).

All of this works well with the exception of a 1 and 10. Any time i enter a
yards gained of 10 or greater only on a 1 down the next 2 down and distance
cells will go to 2 down and a negative number. It will never display two
first downs in sequence WHY NOT???

Are there any other functions that might work for this?

Thank you