I am writing a test on REDCAP where the user must enter 5 potential diagnoses based on the data provided. The user will be allowed to enter the answers in any order. When I export, the answers will be exported to excel, but each correct diagnosis must be placed into a specific cell. This is being done this way for grading ease (and potentially to incorporate this into REDCAP in the future for immediate user feedback).
My excel sheet looks like this, the correct answers are 3, 5, 6, 7, 8
| Diagnoses | User Entered Diagnosis | Diagnosis 1 | Diagnosis 2 | Diagnosis 3 | Diagnosis 4 | Diagnosis 5 | |
|---|---|---|---|---|---|---|---|
| Diagnosis 1: | 3 | 3 | 5 | 6 | 7 | 8 | |
| Diagnosis 2: | 5 | ||||||
| Diagnosis 3: | 6 | ||||||
| Diagnosis 4: | 7 | ||||||
| Diagnosis 5: | 8 |
If a user entered the answers in this order (5,6,3,8,7), I created an IF statement to assign each of the answers to its designated cell: (3,5,6,7,8)
| Diagnoses | User Entered Diagnosis | Diagnosis 1 | Diagnosis 2 | Diagnosis 3 | Diagnosis 4 | Diagnosis 5 | |
|---|---|---|---|---|---|---|---|
| Diagnosis 1: | 5 | 3 | 5 | 6 | 7 | 8 | |
| Diagnosis 2: | 6 | ||||||
| Diagnosis 3: | 3 | ||||||
| Diagnosis 4: | 8 | ||||||
| Diagnosis 5: | 7 |
I created the following IF statement for each of the designated cells.
=IF(B2=3,3,IF(B3=3,3,IF(B4=3,3,IF(B5=3,3,IF(B6=3,3,IF(AND(B2<>3, B2<>5,B2<>6,B2<>7,B2<>8),0))))))
Which works, but, if a user enters an incorrect statement, it looks like this
| Diagnoses | User Entered Diagnosis | Diagnosis 1 | Diagnosis 2 | Diagnosis 3 | Diagnosis 4 | Diagnosis 5 | |
|---|---|---|---|---|---|---|---|
| Diagnosis 1: | 2 | 3 | FALSE | FALSE | 7 | 8 | |
| Diagnosis 2: | 3 | ||||||
| Diagnosis 3: | 7 | ||||||
| Diagnosis 4: | 8 | ||||||
| Diagnosis 5: | 9 |
And I want it to look like this:
| Diagnoses | User Entered Diagnosis | Diagnosis 1 | Diagnosis 2 | Diagnosis 3 | Diagnosis 4 | Diagnosis 5 | |
|---|---|---|---|---|---|---|---|
| Diagnosis 1: | 2 | 3 | 2 | 9 | 7 | 8 | |
| Diagnosis 2: | 3 | ||||||
| Diagnosis 3: | 7 | ||||||
| Diagnosis 4: | 8 | ||||||
| Diagnosis 5: | 9 |
I am having trouble figuring out the logic to allow for this. I tried various different forms, but none have worked. Because I am going to be adapting this REDCAP in the future, I need to use IF, AND, OR statements only. Thank you so much for your time.