Nested IF

[Excel Noobie94]

Hello,

This my first post. I have been trying to teach myself how to perform an advanced IF(), IF(AND(), and IF(OR() for a workbook for my job. The scenario is the following parameters:


Scenario Parameters:
There are three categories to check. If any are “Not Compliant” but at least one is “Compliant”, then the overall rating is “Partial Compliant”. If a category is marked as "N/A" then it should have no effect on the score of the overall compliance category unless all subcategories are marked as N/A. For example, Sections 2.1-2.7 have their drop down menus selected and output an overall score. Once these sections are completely filled out the overarching category for section 2 should output a score based on the parameters. The score is not a number but one of the phrases designated above. This score is then calculated based upon the above scenario parameters for determining the overall score for section 2.

I have created cells to reference for these category designation so that I do not have to continually manually type out the phrases partial Compliant, Compliant, Not Compliant and N/A. I have also created Cell Validation drop down menus so once a team member makes their drop down choices for each sub section the scores will determine the overall score for the overall category.


Key of Cell References:
E3= Compliant
E4= Not Compliant
E5= N/A
E6 = Partial Compliant

Sections Key:
E13= Overall Section 2
E14=Section 2.1
E18 = Section 2.2
E23 = Section 2.3
E26 = Section 2.4
E34 = Section 2.5
E38 = Section 2.6
E41 = Section 2.7


Cell E13 is where I am having trouble with my formula outputting the correct response when I have some N/A mixed in with only Compliant and vice versa with Not Compliant mixed in with N/A. If there is at least one sub section with a "Compliant" score and one section with a "Not Compliant" score then automatically the overall is "Partial Compliant". The N/A is what is breaking my logic in my formula.

QUESTIONS:
Is there a way to write the formula ignore the N/A option unless all sub sections equal N/A?

Here is what I have currently:


=IF(AND($E$14=$E$3,$E$18=$E$3,$E$23=$E$3,$E$26=$E$3,$E$34=$E$3,$E$38=$E$3,$E$41=$E$3),"Compliant",IF(OR($E$14=$E$3,$E$18=$E$3,$E$23=$E$3,$E$26=$E$3,$E$34=$E$3,$E$38=$E$3,$E$41=$E$3),"Partial Compliant",IF(AND($E$14=$E$4,$E$18=$E$4,$E$23=$E$4,$E$26=$E$4,$E$34=$E$4,$E$38=$E$4,$E$41=$E$4),"Not Compliant", IF(AND($E$14=$E$5,$E$18=$E$5,$E$23=$E$5,$E$26=$E$5,$E$34=$E$5,$E$38=$E$5,$E$41=$E$5),"N/A"))))

I am reading this as the following....."If sections 2.1-2.7 all equal "Compliant" then the overall category score is "Compliant". If any of the sections equal "Compliant" then the overall is "Partial Compliant". If section 2.1-2.7 all equal "Not Compliant" then the overall category score is "Not Compliant". If section 2.17-2.7 all equal N/A then the overall score is "N/A".


It is not working as intended when factoring in the "N/A" scores for sub sections. I basically need the "N/A' to be ignored unless all sub sections are "N/A".

NOTE: When opening the workbook the subsections will read as False and that is because the drop down selections have not been selected yet. Once selections have been made then it will not read False.

 

[GraH - Guido]

Wow, that sounds overcomplicated.

Suggestion, use a proper scoring:
- Compliant = 1
- Non Compliant = 0
- N/A = N/A (text, not the function)

Nested if becomes something like
If SUM = COUNT then "Compliant"
else if SUM > 0 then "partial Compliant"
else if COUNTIF(RANGE, "N/A") = COUNT then "N/A"
else "Non Compliant"

 

[Excel Noobie94]

I was experimenting with this as well! This is incredibly helpful. I will report back here with my progress. Thanks for the insights.