Forum Discussion
Use Excel to perform my data interpration
Hi,
I have been performing this Data Interpretation on my own and I would like to use Excel to automatically perform this data analysis for me if possible: There are 8 possible scenarios and 4 different interpretation results. Here is the logic, the Interpretation is:
COVID-19 Positive if any 2, or all three of S, N or O are Pres (Present) regardless of MS2 result.
Indeterminate if any 1 of S, N or O are Pres AND MS2 is Pres.
Invalid if 1 or none of S, N or O are Pres AND MS2 is Abs (Absent).
Negative if all three of S, N or O are Abs AND MS2 is Pres.
I have charted every possible combination there could be and there are 16.
| ID | S | N | O | MS2 | Interpretation | |
| 1 | Abs | Abs | Abs | Abs | Invalid | |
| 2 | Pres | Abs | Abs | Abs | Invalid | |
| 3 | Pres | Abs | Abs | Pres | Indeterminate | |
| 4 | Pres | Pres | Abs | Abs | COVID-19 Pos | |
| 5 | Pres | Pres | Abs | Pres | COVID-19 Pos | |
| 6 | Pres | Pres | Pres | Abs | COVID-19 Pos | |
| 7 | Pres | Pres | Pres | Pres | COVID-19 Pos | |
| 8 | Abs | Pres | Abs | Abs | Invalid | |
| 9 | Abs | Pres | Abs | Pres | Indeterminate | |
| 10 | Abs | Pres | Pres | Abs | COVID-19 Pos | |
| 11 | Abs | Pres | Pres | Pres | COVID-19 Pos | |
| 12 | Abs | Abs | Pres | Abs | Invalid | |
| 13 | Abs | Abs | Pres | Pres | Indeterminate | |
| 14 | Pres | Abs | Pres | Abs | COVID-19 Pos | |
| 15 | Pres | Abs | Pres | Pres | COVID-19 Pos | |
| 16 | Abs | Abs | Abs | Pres | Negative |
What would be the SIMPLEST way to have the interpretation auto populate? Ideally I would like to use a formula if possible rather than doing Pivot tables.
=IF(AND(COUNTIF(B2:D2,"Abs")=3,E2="Pres"),"Negative",IF(AND(COUNTIF(B2:D2,"Pres")<=1,E2="Abs"),"Invalid",IF(COUNTIF(B2:D2,"Pres")>=2,"COVID-19 Pos",IF(AND(COUNTIF(B2:D2,"Pres")=1,E2="Pres"),"Indeterminate",""))))
You can try a nested IF formula.
6 Replies
In row 2:
=LET(n,COUNTIF(B2:D2,"Pres"),IFS(n>=2,"COVID-19 Positive",E2="Abs","Invalid",n=1,"Indeterminate",n=0,"Negative"))- Thank you so much for this formula it worked! i was so amazed. How do you learn how to make them? I would love to learn. Thank you.
Exceljet is an excellent site to learn about Excel functions and formulas.
=IF(AND(COUNTIF(B2:D2,"Abs")=3,E2="Pres"),"Negative",IF(AND(COUNTIF(B2:D2,"Pres")<=1,E2="Abs"),"Invalid",IF(COUNTIF(B2:D2,"Pres")>=2,"COVID-19 Pos",IF(AND(COUNTIF(B2:D2,"Pres")=1,E2="Pres"),"Indeterminate",""))))
You can try a nested IF formula.
- Thank you so much for this nested if formula it worked beautifully!
