If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula Question
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! |
#2
|
|||
|
|||
Formula Question
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 "Ben" wrote: 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! |
#3
|
|||
|
|||
Formula Question
=MOD(A2,500)
-- David Biddulph "Ben" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|