Scoring spreadsheet that needs to reset to zero if score goes negative

I am trying to create a scoring spreadsheet for a game. In this game if you fail to score three turns in a row you lose 1000 points. However if that takes you score into a negative number then you score simply goes to zero and you start scoring again from there. The spreadsheet has 20 rows for the 20 turns for each player. At the top it has a running total. It is this running total where I am stuck. I started off using a simple SUM formula to add up the rows in the column. eg. 100+300 +400 gives the running total of 800. Problem is that if that player fails to score for the next three turns they get -1000 points so their score would return to 0. Only points scored from there on would be counted in the running total, so the sum of the whole column no longer applies. Any ideas out there?

It would help if you attached a sample Excel workbook, so we could see what you are trying to describe in context.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Thanks Pete, I have attached an example (I hope). In this example after round 6 the score goes to -200, but should actually reset to 0. Then after Round 7 the Total should be 0+100, so 100.

Cheers
Rick

I suggest that you use column C to give a running cumulative total, with this formula in C3:

=IF(B3=-1000,0,IF(B3="Fail",0,B3)+C2)

Copy this down to the bottom (i.e. C22), even if you don't yet have data for all of column B, then C22 will always show the latest position, i.e. the formula in B2 can be:

=C22

Hope this helps.

Pete

Thanks Pete, that was kind of the direction I was heading, but you shortened my journey considerably. If you would like another challenge. . . Can you think of a way of, rather than entering -1000 for the third fail, you simply enter Fail again and the running total in Cloumn C recognises the three onecutive fails and deducts the 1000 points?

Cheers
Rick

Pete, in applying your formula it seems that if B3=-1000 then it just retuns a value of 0. However I need it to remove 1000 from the running total and if this is less than 0 then set the value to 0. For example, if the running total is 1300 and then the next score is 300 it should return a value of 600. Your formula returns a value of 300 because the total was set to 0. So it needs another If statement to check if the total is <0. I have attached another copy.

Cheers
Rick