dgoscinski
New Member
My question: I want to do more than a simple count. I want to do a COUNT of wrong answers during a turn of questions. There are 3 weighted thresholds. 1st threshold counts all wrong answers between 1-6 players as only 1. (All wrong answers=1). In the 2nd threshold, which starts at count of 5, 1 wrong answer=1, 2+ wrong answers=2. The 3rd threshold starts at count of 9. 1 wrong answer =1, 2 wrong=2, 3+ =3. 
Picture 20+ squares in a row. First 4 spaces have a "speed limit" of 1. (count up to 4 1 at a time). Spaces 5-8 have a limit of 2 (wrong answers cannot move you further than 2 spaces). Spaces 9-12 have a limit of 3 (wrong answers can't move you more than 3). Space 12 and after # of wrong answers = number of spaces. (Column H--simple count).
More in-depth explanation: I am creating a type of answer tracker on a game board for my students. There are up to 6 players. They have a monster chasing them in this game. But I want to give them a head start.
Here is a screenshot with formulas I have.
Column A is the Turn. (my question asked)
Columns B-G are each player and whether they got the answer correct or not.
Column H is a count of the number of wrong answers for that round.
Column I is a helper column that begins to count when the count of a wrong answer in a round reaches 4.
Column N shows where the monster would be on the game board.
The game board would look like this: the first 4 spaces, Monster moves 1 space at at time. From space 5-8, the monster COULD move 2 spaces. From spaces 9-12, the monster COULD move 3 spaces. If No one gets a wrong answer, the monster does not move.
So to read the screenshot. In the first 2 rounds of questions, no student go a wrong answer. The monster is not on the board. (N2 and N3)
In the 3rd round, 1 student (h4) got an incorrect answer. The monster moves 1 space. Now on square 1 (n4)
In the 4th round, another student got an incorrect answer. The monster moves 1 space. Now on square 2 (n5). (Had more students gotten answer incorrect, monster would have still only moved 1 space. In the first "threshold" of numbers.)
In Round 5, 2 students had incorrect answer. (h6). However, again the monster only moves one space to Square 3 (n6).
Round 6, 1 student gets incorrect (h7). The monster moves one space to square 4. One more wrong answer and the monster will be in 2nd threshold, where wrong answers moves it 2 spaces...but not there yet. (n7). (In addition, I7 reads 4. It has counted the number of incorrect rounds of answers)
Round 7, no wrong answers. Monster still on square 4. (n8)
Round 8, 1 wrong answer. (h9) Because we are now into the 2nd threshold, the monster has the ability to move up to 2 spaces. If 2 or more students had been incorrect, the monster WOULD HAVE moved 2. But only 1 was wrong and so monster moves 1 spot to Square 5. (n9)
Round 9, 2 incorrect answers. The monster can move 2 spaces. (not to exceed 2). This puts the monster on square 7.
Round 10, 1 incorrect answer. The monster only moves one space. However, the next threshold is one space away. There the monster limit will be 3 spaces. All wrong answers 3+ will ONLY allow monster to move 3 spaces.
So what I can't figure out is the How to make the dynamic work. I do have a COUNTIF formula in Column I that essentially alerts me when the first threshold of 4 is crossed. I want to count all wrong answers in a round as 1 until the count reaches 4. Then I want all wrong answers greater than 2 to count as 2, 1, or 0 in the 2nd threshold. (When monster reaches square 9) then the 3rd threshold: greater than 3 counts as 3, 2 wrong =2, and 1 wrong =1. Once the monster reaches square 13+, it moves exactly the number of wrong answers (column h). So if all 6 students get a wrong answer, monster moves 6 spaces.
Picture 20+ squares in a row. First 4 spaces have a "speed limit" of 1. (count up to 4 1 at a time). Spaces 5-8 have a limit of 2 (wrong answers cannot move you further than 2 spaces). Spaces 9-12 have a limit of 3 (wrong answers can't move you more than 3). Space 12 and after # of wrong answers = number of spaces. (Column H--simple count).
More in-depth explanation: I am creating a type of answer tracker on a game board for my students. There are up to 6 players. They have a monster chasing them in this game. But I want to give them a head start.
Here is a screenshot with formulas I have.Column A is the Turn. (my question asked)
Columns B-G are each player and whether they got the answer correct or not.
Column H is a count of the number of wrong answers for that round.
Column I is a helper column that begins to count when the count of a wrong answer in a round reaches 4.
Column N shows where the monster would be on the game board.
The game board would look like this: the first 4 spaces, Monster moves 1 space at at time. From space 5-8, the monster COULD move 2 spaces. From spaces 9-12, the monster COULD move 3 spaces. If No one gets a wrong answer, the monster does not move.
So to read the screenshot. In the first 2 rounds of questions, no student go a wrong answer. The monster is not on the board. (N2 and N3)
In the 3rd round, 1 student (h4) got an incorrect answer. The monster moves 1 space. Now on square 1 (n4)
In the 4th round, another student got an incorrect answer. The monster moves 1 space. Now on square 2 (n5). (Had more students gotten answer incorrect, monster would have still only moved 1 space. In the first "threshold" of numbers.)
In Round 5, 2 students had incorrect answer. (h6). However, again the monster only moves one space to Square 3 (n6).
Round 6, 1 student gets incorrect (h7). The monster moves one space to square 4. One more wrong answer and the monster will be in 2nd threshold, where wrong answers moves it 2 spaces...but not there yet. (n7). (In addition, I7 reads 4. It has counted the number of incorrect rounds of answers)
Round 7, no wrong answers. Monster still on square 4. (n8)
Round 8, 1 wrong answer. (h9) Because we are now into the 2nd threshold, the monster has the ability to move up to 2 spaces. If 2 or more students had been incorrect, the monster WOULD HAVE moved 2. But only 1 was wrong and so monster moves 1 spot to Square 5. (n9)
Round 9, 2 incorrect answers. The monster can move 2 spaces. (not to exceed 2). This puts the monster on square 7.
Round 10, 1 incorrect answer. The monster only moves one space. However, the next threshold is one space away. There the monster limit will be 3 spaces. All wrong answers 3+ will ONLY allow monster to move 3 spaces.
So what I can't figure out is the How to make the dynamic work. I do have a COUNTIF formula in Column I that essentially alerts me when the first threshold of 4 is crossed. I want to count all wrong answers in a round as 1 until the count reaches 4. Then I want all wrong answers greater than 2 to count as 2, 1, or 0 in the 2nd threshold. (When monster reaches square 9) then the 3rd threshold: greater than 3 counts as 3, 2 wrong =2, and 1 wrong =1. Once the monster reaches square 13+, it moves exactly the number of wrong answers (column h). So if all 6 students get a wrong answer, monster moves 6 spaces.
