Adding series in a table and breaking again when the value reaches a limit

[adriandwor]

Hey everyone,

I'm working on a school project and maybe one of you can help me with the following excel problem. I tried to google a little bit but I can't properly formulate my question.

I have a table in excel that looks like this:

I'd like to be able to sum contents in U column automatically until the result reaches 235 and then start again from 0. Column B represents different days of the week. When a day changes, the calculations also should start from 0. There are about 5000 data points. Can anyone help?

Thanks a lot

 

[jasonb75]

Try this in V2 and fill down.

=IF(N(V1)+U2>235,U2,N(V1)+U2)

Noting that you have a structured table, it may behave a little erratically when you add new rows, or if you try sorting. If that happens it may be necessary to re-enter the formula into V2 and fill down again. There are fixes to prevent the problems but often make things more complicated than they should be.

 

[Eric W]

This version also resets when the day changes:

=IF(OR(B2<>B1,N(V1)+U2>235),U2,N(V1)+U2)

 

[jasonb75]

Cheers, Eric

I completely missed the change of day requirement.

 

[adriandwor]

This version also resets when the day changes:

=IF(OR(B2<>B1,N(V1)+U2>235),U2,N(V1)+U2)

Absolutely brilliant. Thank you so much. Also thanks Jasonb75 for the little explanation

 

[Eric W]

Glad we could help!