• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying to create a count based on thresholds, sort of a handicap

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. 74739

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.
74738 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.
 

Attachments

p45cal

Well-Known Member
One way, I'm sure it could be slicker, in the attached:
In column H, much as you had it, a count of Nos in each turn.
In the next 4 columns (I,J,K & L) is the number of moves that the score in column H would translate to for each of the 4 zones.
Columns M and N work together: The top rows in these columns are just seed values, the formulae in row 3 of the sheet are the ones you can copy down. Column N is the result column.
Column M is the main one which works out which zone you're in by looking at the last position and using a lookup table of one column. I've put that lookup table in cells V5:V8 but you don't really need it, you can hard-code the values into the formula as I have done in one cell, M7.

You can put these last two columns into 1, which I've done on sheet MonsterSimulation1 (2) in column M.

ps. you realise you posted a .csv, not a workbook, so there were no formulae?
 

Attachments

p45cal

Well-Known Member
Finally, if you wanted to get it all in one column with no helper cells/columns at all:
in cell H2:
=MIN(COUNTIF(B2:G2,"No"),1)
and in cell H3:
=INDEX(IF(COUNTIF(B3:G3,"No")>{1,2,3,999},{1,2,3,999},COUNTIF(B3:G3,"No")),MATCH(H2,{0;5;9;13}))+H2
copied down.
Depending on your version of Excel, you may have to array-enter the formula in H3. This means that instead of committing the formula to the sheet using plain Enter on the keyboard, you hold down the Ctrl+Shift while pressing Enter.
 

dgoscinski

New Member
Finally, if you wanted to get it all in one column with no helper cells/columns at all:
in cell H2:
=MIN(COUNTIF(B2:G2,"No"),1)
and in cell H3:
=INDEX(IF(COUNTIF(B3:G3,"No")>{1,2,3,999},{1,2,3,999},COUNTIF(B3:G3,"No")),MATCH(H2,{0;5;9;13}))+H2
copied down.
Depending on your version of Excel, you may have to array-enter the formula in H3. This means that instead of committing the formula to the sheet using plain Enter on the keyboard, you hold down the Ctrl+Shift while pressing Enter.
I apologize. Rookie mistake. I did NOT know that .csv didn't save the formulas. SMH.
 

dgoscinski

New Member
One way, I'm sure it could be slicker, in the attached:
In column H, much as you had it, a count of Nos in each turn.
In the next 4 columns (I,J,K & L) is the number of moves that the score in column H would translate to for each of the 4 zones.
Columns M and N work together: The top rows in these columns are just seed values, the formulae in row 3 of the sheet are the ones you can copy down. Column N is the result column.
Column M is the main one which works out which zone you're in by looking at the last position and using a lookup table of one column. I've put that lookup table in cells V5:V8 but you don't really need it, you can hard-code the values into the formula as I have done in one cell, M7.

You can put these last two columns into 1, which I've done on sheet MonsterSimulation1 (2) in column M.

ps. you realise you posted a .csv, not a workbook, so there were no formulae?
You nailed it! Thank you so much for the help. I was definitely over-thinking the problem. The I,J,K,&L made it SO much more workable than I had. Nicely done.
 
Top