# Set max cell value but restart count from that value?

#### matmanlives

##### New Member
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

#### Attachments

• ssheet.png
42.1 KB · Views: 15

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Phuoc

##### Well-known Member
Try this in cell AO3:

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

#### matmanlives

##### New Member
=IF(SUM(C3:AN3)=0,0,MOD(SUM(C3:AN3)-1,11)+1)
perfect.
works like a charm.
Thank you

#### Phuoc

##### Well-known Member
You're welcome.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,621
Messages
5,838,404
Members
430,546
Latest member
CometOz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back