Set max cell value but restart count from that value?

[matmanlives]

hi
i am trying to automate a count function that will cap out at 11 and then restart the count.
first a bit of background info.

we play a game based on football teams scoring goals. if your team lands on EXACTLY 11 goals, you win the prize pot. E.G if your team has scored 9 goals, and then in the next game they score 3 goals. you take the pot. If they score 4 goals you are left on 1 as your count restarts. (there is a chance i may not be explaining this as clearly as i think i am ) takes a bit of looking after and was hoping to make it easier to track.

What i would like to happen is that when I enter the team goals in the relevant weeks, the cumulative (sp) total should update. BUT, if the total goes over 11, then the count should restart at 1.
I.e "Aston villa" should now be showing 4, not 15.
It then becomes a lot more complicated when we get into the realms of 23+ goals as there would be 2 instances of 11 being taken away.

any help would be very much appreciated
Thanks guys

 

[Phuoc]

Try this in cell AO3:

=IF(SUM(C3:AN3)=0,0,MOD(SUM(C3:AN3)-1,11)+1)

 

[matmanlives]

=IF(SUM(C3:AN3)=0,0,MOD(SUM(C3:AN3)-1,11)+1)

perfect.
works like a charm.
Thank you

 

[Phuoc]

You're welcome.