Can the 4 lengthy formulae be placed as a table ?

[shili12]

right now the formulae are quite long. is there a way they can be placed as a table , and same results returned, i tried to place, but not successful, therefore ignore my table worksheets in xlsx. or if there is another simpler way to view , PQ,VBA, LET, all are OK to use, i use office 365.

=SWITCH(TRUE,AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="C",V5="TPO"),SWITCH(P5,11,11/12*3881,9,9/12*3881,10,10/12*3881,""),AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="B",V5="TPO"),SWITCH(P5,11,11/12*4150,9,9/12*4150,10,10/12*4150,""),AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="A",V5="TPO"),SWITCH(P5,11,11/12*4200,9,9/12*4200,10,10/12*4200,""),AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="D",V5="TPO"),SWITCH(P5,11,11/12*1870,9,9/12*1870,10,10/12*1870,""),AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="C",V5="COMP"),SWITCH(P5,1,1/12*3.5%*W5,11,11/12*3.5%*W5,9,9/12*3.5%*W5,10,10/12*3.5%*W5,""),AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="d",V5="COMP"),SWITCH(P5,1,1/12*3%*W5,11,11/12*3%*W5,9,9/12*3%*W5,10,10/12*3%*W5,""),AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="B",V5="COMP"),SWITCH(P5,1,1/12*4.5%*W5,11,11/12*4.5%*W5,9,9/12*4.5%*W5,10,10/12*4.5%*W5,""),AND(COUNTIF(Q:Q,Q5)>=2,SUMIF(Q:Q,Q5,P)=12,LEFT(E5,1)="A",V5="COMP"),SWITCH(P5,1,1/12*5%*W5,11,11/12*5%*W5,9,9/12*5%*W5,10,10/12*5%*W5,""),"")

 

[Frank2021]

Hello,


Take a look at the formulas in columns AA and AB.
If you liked it, give it a thumbs up.

Regards

 

[shili12]

the formulas are modified, but can i have it as a Table then lookup from the same ??

 

[Frank2021]

Hello,

Now they are in table format.

Regards.

 

[shili12]

Lengthy formula but 2 x 4 simple table ?