tjdickinson
Board Regular
 Joined
 Jun 26, 2021
 Messages
 59
 Office Version

 365
 Platform

 Windows
In my workbook, the tab 'Data' includes the names of students who have completed either of two tests. (Note the actual workbook goes to column CG with 72 questions.)
The test questions for both tests are arranged in the sheet called 'Test Questions':
Everything is presented in a sheet called 'Results': I select the student's name from a drop down list, and it shows me the results, the notes, and each question, with the correct answer and the student's incorrect answer indicated, connected to a spinner. This allows me to go over each student's results with them without disclosing another student's results.
(NB: Results!C2 has an ActiveX dropdown linked to C2 and pulling the names from Data!D2:D150. Results!B12 has a conditional format indicating the difficulty of the question. Results!B13 has spinner arrows linked to Results!B12 to navigate the question number. Results!B14:B17 contain formulas which indicate the correct answer and the student's incorrect answer; the symbols are fontdependent (Wingdings).)
Now, my question:
In a new sheet 'Stats', I want to extract from 'Data' a count of how many students gave each answer.
I'm looking for a formula I can (relatively easily) copy and paste into all the cells to do the count. I don't want to spend ages changing references in each cell.
Basically, the formula in Stats!B2 (and similarly for the other cells) needs to:
Finally, I want to add a data bars conditional format to Results!C14:C17 which indicates the corresponding data from the Stats! worksheet (how many people gave that response). Of course, it needs to identify the test number from Results!D2 to know which set of data to use.
Thank you very much for any help you can give me!
20212022 CEFR Test Results.xlsx  

B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z  AA  AB  AC  AD  
1  First name  Surname  Student Name  Test  Duration  Low EL/IN  High EL/IN  Low PI/UI  High PI/UI  Difficulty  Confidence  Notes  1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  
2  Bob  Hope  Bob Hope  2  
3  Jimmy  Stewart  Jimmy Stewart  2  32:05  83  94  78  89  somewhat difficult  somewhat confident  A  A  C  
4  Grace  Kelly  Grace Kelly  1  15:29  94  94  72  89  somewhat easy  not very confident  D  
5  Cary  Grant  Cary Grant  2  21:44  78  89  61  56  somewhat easy  somewhat confident  C  A  D  B  
Data 
The test questions for both tests are arranged in the sheet called 'Test Questions':
Everything is presented in a sheet called 'Results': I select the student's name from a drop down list, and it shows me the results, the notes, and each question, with the correct answer and the student's incorrect answer indicated, connected to a spinner. This allows me to go over each student's results with them without disclosing another student's results.
20212022 CEFR Test Results.xlsx  

A  B  C  D  E  F  G  H  I  J  K  
1  Class  Student Name  Test  Duration  Low EL/IN  High EL/IN  Low PI/UI  High PI/UI  Difficulty  Confidence  
2  5LAT  Grace Kelly  2  15:29  94  94  72  89  somewhat easy  not very confident  
3  
4  Notes:  0  
5  
6  
7  
8  
9  
10  
11  Question  
12  1  A: Do you like my pasta sauce? B: It ... delicious!  
13  
14  ü  A) tastes  
15  B) will taste  
16  C) is tasting  
17  D) has tasted  
18  
Results 
(NB: Results!C2 has an ActiveX dropdown linked to C2 and pulling the names from Data!D2:D150. Results!B12 has a conditional format indicating the difficulty of the question. Results!B13 has spinner arrows linked to Results!B12 to navigate the question number. Results!B14:B17 contain formulas which indicate the correct answer and the student's incorrect answer; the symbols are fontdependent (Wingdings).)
Now, my question:
In a new sheet 'Stats', I want to extract from 'Data' a count of how many students gave each answer.
20212022 CEFR Test Results.xlsx  

A  B  C  D  E  F  G  H  I  J  K  
1  Question  1A  1B  1C  1D  1U  2A  2B  2C  2D  2U  
2  1  
3  2  
4  3  
Stats 
I'm looking for a formula I can (relatively easily) copy and paste into all the cells to do the count. I don't want to spend ages changing references in each cell.
Basically, the formula in Stats!B2 (and similarly for the other cells) needs to:
 Compare LEFT(Stats!B1,1) to Data!$E$2:$E$150 to know which rows to include
 Count the number of nonblank cells in Data!$F$2:$F$150 to know how many people have completed the test (only counting those rows with matching test number)
 Compare RIGHT(Stats!B1,1) to 'Test Questions'!$G$2:$G$73 if Test 1 or !$M$2:$M$73 if Test 2 to determine whether this option was the right answer for the question
 If TRUE: then count nonblank cells in the column of array Data!$N$2:$CG$150 with index equal to Stats!A2 (so, A2=1 thus column N, A3=2 thus column O, A4=3 thus column P, etc.), but only counting the rows in which column E matches LEFT(Stats!B1,1). This should count the number of students who answered the question correctly.
 If FALSE: count the number of times RIGHT(Stats!B1,1) appears in the column of array Data!$N$2:$CG$150 with index equal to Stats!A2 (so, A2=1 thus column N, A3=2 thus column O, A4=3 thus column P, etc.), but again only counting rows in which column E matches LEFT(Stats!B1,1). This counts the number of times this incorrect answer was selected.
Finally, I want to add a data bars conditional format to Results!C14:C17 which indicates the corresponding data from the Stats! worksheet (how many people gave that response). Of course, it needs to identify the test number from Results!D2 to know which set of data to use.
Thank you very much for any help you can give me!