Using multiple nested IF-functions in Excel, is this the right solution?-CodePudding

I am attempting to create an assessment tool with Excel, that uses a set criteria. The user can customise the tool by tagging each criteria to a dimension e.g. Criteria 1 relates to organisation, Item 2 relates to Tooling etc. There are 6 levels of criteria, but not all dimensions tagged will have a criteria item for each level. This is specified in a table (ITILAssessmentTbl) within a sheet.

To make this easy to understand and use, i have decided to use a matrix that maps out the criteria the levels and also the user specific category e.g. Tooling. However, there will not be criteria items for each levels 1-6 under each category. I do not want to show blanks in the matrix (See image 1) so have tried to use a nested IF formula to identify if there criteria items under the level and header, and if not, move to the next level.

The formula pasted below works for column A, however, does not work for Column B.

I would like to keep this within Excel (No VBA) as it there are some security issues with Macro, and I am unable to upload a blank workbook. Is there a better method that nested IF()- functions?

=IF(TEXTJOIN("/", TRUE, IF((($D$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($D$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($E$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($E$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($F$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($F$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($G$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($G$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($H$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($H$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($I$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($I$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),""))))))

CodePudding user response：

First of all, please replace the =IF(x * y, ...) by =IF(AND(x,y), ...). It does not modify behaviour, but it increases readability.
Next to that, you do something weird: you check a condition. If it is met, you glue some text together, and afterwards you check if the glued text is empty or not.
Why? This only seems to make sense if, for some reason, the "some text" is empty, which you can check far better, using the length of that text, like IF(LEN(ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]])>0, ...).

CodePudding user response：

This is not yet technically a proper answer but I will edit it to be "the answer" if we figure this out. I need the space and the formatting to show this, so I can't make it a comment.

You have O365, and so you also have access to the LET function that will change your Excel life. I do consulting work that often centers on the limit of human capacity in terms of complexity, and you have created a formula that is sort of beyond the complexity horizon. So let's solve that first with the LET function. By using the LET function and alt-Enter to create non-breaking return, this is the exact replication of your formula:

=LET(
key, ITILAssessmentTbl[[Key]:[Key]],
secDim, ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]],
ITIL, ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]],
tj_D, TEXTJOIN("/", TRUE, IF((($D$5=key)*($C7=secDIM)), ITIL, "")),
tj_E, TEXTJOIN("/", TRUE, IF((($E$5=key)*($C7=secDIM)), ITIL, "")),
tj_F, TEXTJOIN("/", TRUE, IF((($F$5=key)*($C7=secDIM)), ITIL, "")),
tj_G, TEXTJOIN("/", TRUE, IF((($G$5=key)*($C7=secDIM)), ITIL, "")),
tj_H, TEXTJOIN("/", TRUE, IF((($H$5=key)*($C7=secDIM)), ITIL, "")),
tj_I, TEXTJOIN("/", TRUE, IF((($I$5=key)*($C7=secDIM)), ITIL, "")),
IF(tj_D<>"",tj_D,IF(tj_E<>"",tj_E,IF(tj_F<>"",tj_F,
IF(tj_G<>"",tj_G,IF(tj_H<>"",tj_H,IF(tj_I<>"",tj_I,""))))))
)

We've now separated the logic of your nested IFs from the complexity of how you gathered the data. And it's now much easier to spot:

subtle typos in your TEXTJOIN functions, since they are all identical save for 1 character;
A logic error in the nesting structure of the IF functions.

I'll use "pop" to denote "populated", meaning "not blank" in my pseudocode below. so "Dpop" means "D is not blank." We can see that the logic is:

IF Dpop THEN return textjoin D
ELSEIF Epop THEN return textjoin E
ELSEIF Fpop THEN return textjoin F
ELSEIF Gpop THEN return textjoin G
ELSEIF Hpop THEN return textjoin H
ELSEIF Ipop THEN return textjoin I
ELSE return ""
ENDIF

So now you can "examine your logic in peace" and see if it is what you want. If the nested IF()s are correctly nested, then you know you problem lies elsewhere.

A couple of thoughts:

Not sure if you want or need to make your column references [[Key]:[Key]], etc. You may actually just want ITILAssessmentTbl[Key]. And this applies to all 3 of your column references.
You have access to the IFS() statement and that, like LET(), changes everything.

Nested IF()s would become a single IF-ELSEIF-ELSE_IF...ENDIF in one function: IFS(tj_D<>"",tj_D,tj_E<>"",tj_E,tj_F<>"",tj_F,tj_G<>"",tj_G,tj_H<>"",tj_H,tj_I<>"",tj_I,TRUE,"")

And it gets better. Your whole function, using LET and IFS would be:

=LET(
key, ITILAssessmentTbl[[Key]:[Key]],
secDim, ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]],
ITIL, ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]],
tj_D, TEXTJOIN("/", TRUE, IF((($D$5=key)*($C7=secDIM)), ITIL, "")),
tj_E, TEXTJOIN("/", TRUE, IF((($E$5=key)*($C7=secDIM)), ITIL, "")),
tj_F, TEXTJOIN("/", TRUE, IF((($F$5=key)*($C7=secDIM)), ITIL, "")),
tj_G, TEXTJOIN("/", TRUE, IF((($G$5=key)*($C7=secDIM)), ITIL, "")),
tj_H, TEXTJOIN("/", TRUE, IF((($H$5=key)*($C7=secDIM)), ITIL, "")),
tj_I, TEXTJOIN("/", TRUE, IF((($I$5=key)*($C7=secDIM)), ITIL, "")),
IFS(
tj_D<>"",tj_D,
tj_E<>"",tj_E,
tj_F<>"",tj_F,
tj_G<>"",tj_G,
tj_H<>"",tj_H,
tj_I<>"",tj_I,
TRUE,"")
)