# 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: 4

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Phuoc

##### Active 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

You're welcome.

Replies
1
Views
262
Replies
1
Views
64
Replies
0
Views
107
Replies
12
Views
259
Replies
4
Views
343

1,129,549
Messages
5,636,945
Members
416,953
Latest member
prakashkumar

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