Formula Question

[Ben]

I work in a school and I need to tally the number of tickets grade levels
earn. What I am stuck on is, I want to tally the tickets earned, but when
they hit a certain number, the tally will start over again moving any carry
over ticket tallys with it. For example, the first grade has to earn 500
tickets. They end up earning 535. I want the tally to stop at 500 and move
that 35 into a new tally (rolling over the extra 35). Any help would be a
appreciated! Thanks!

 

[Mike H]

Hi,

Lets say the ticket values are in a1 - a400.

use this for the first 500
=MIN(500,SUM($A$1:$A$400))

and for the next 500
=MIN(1000,MAX(0,SUM($A$1:$A$400)-500))

and finally the rest. This formula counts everything in excess of the first
2 so if it isn't the last change 10^22 to the next threshold and follow the
logic to create the next formula.

=MIN(10^22,MAX(0,SUM($A$1:$A$400)-1500))


Mike