This is not yet a proper answer but I need the additional space and formatting of the answer block to propose a solution that may lead to the answer. The SE purists won't like this, but I am doing the best I can. :)
Even if you don't have access to LET() or IFS(), I will use it here as an interim step to solve your problem because it adds deep clarity to complex nested conditions. We can switch back if we have to once the problem is resolved.
Your function above resolves to the following by using the LET and more intuitive IFS() function:
=LET(
cell_1_7, INDIRECT(ADDRESS(ROW()+1,COLUMN()-7)),
cell_2_7, INDIRECT(ADDRESS(ROW()+2,COLUMN()-7)),
cell_1_0, INDIRECT(ADDRESS(ROW()-1,COLUMN())),
rel, [@RELATIONSHIP],
cond_1, AND(rel="EE",cell_1_7=""),
cond_2, AND(rel="EE",cell_1_7="EE"),
cond_3, AND(rel="EE",cell_1_7="SP",LEFT(cell_2_7)="C"),
cond_4, AND(rel="EE",cell_1_7="SP"),
cond_5, AND(rel="EE",LEFT(cell_1_7)="C"),
cond_6, [@[CONTRACT TIER]]=0,
IFS(rel="", "", cond_1, "E", cond_2, "E", cond_3, "D", cond_4, "S",
cond_5, "C", cond_6, cell_1_0, TRUE, "ERROR")
)
This presentation allows us to see more easily when the result will be D, and that is only when all 4 of these conditions are met:
| Condition |
Which Means |
Must be |
| rel=blank |
@relationship = "" |
False |
| cond_1 |
AND( relationship="EE", cell_1_7="" ) |
False |
| cond_2 |
AND( relationship="EE", cell_1_7="EE" ) |
False |
| cond_3 |
AND( relationship="EE", cell_1_7="SP", LEFT(cell_2_7)="C" ) |
True |
So the question is, does this logic reflect your intentions?
Again, my apologies for using the answer space for clarification, but this is a necessary interim step to provide a proper answer that this will be edited to become.
