Big Nested Ifs formula

[SingSing16]

Hello,

Can you please support me with creation of big formula which will marge 4 small below:

=if(match(("CPS",'Backl') and match("OS",'UnQual','Qual','Prop','Neg','CA') or match("OS",'OW','Book','CR','CW'),'FALSE','TRUE'))

=if(match(("CPS",'Quot') and match ("OS",'UnQual,'Qual','Prop','Neg')or match("OS", 'OW','Book','CR','CW','CA')

,'TRUE','FALSE'))

=if(match(("CPS",'PTB','Shipp','Isnt','Accept','Clin') and match("OS",'UnQal','Qual','Prop','Neg','OW','Book','CR','CA') or match ("OS",'UnQal','Qual','Prop','Neg'), 'FALSE','TRUE'))

=if(match(("CPS",'ORP','IRP') or Match("OS",' '),'Invalid','Invalid'))

Best Regards,

Karolina

[edwin]

if you explain what you are trying to do, what your data looks like then it may be easy for others to help you

[SingSing16]

Hello Edwin,

Thank you so much for your reply.

I think that the best way will be attatching the matrix, where you will see which answer I would like to gain.

I will really appreciate your help.

[edwin]

if you want to maintaing the IF statements then you just cascade them, however you need to reformat these in the following :

if( condition1, Invalid,

     if(condition1, True,

         else False

    )

)

can you tell me if the two fields CPS and OS are in one table?  if so, you can build the logic in your DM by creating a table:
CPS, OS, Status
IPR, UnQual, Invalid
IPR, Qual, Invalid
...
Quot,UnQual, True
Quot, Qual,True
...

then you dont need any if statements.  but that will depend on your DM.  if CPS and OS are in separate tables, it becomes a little bit complicated but at least you dont need to maintain that head scratcher and your charts will be a little bit faster as it will not need to evaluate a cascaded if statement.  also, the NULL will need a little tweaking

[SingSing16]

Hello Edwin, currently they are in the same table. I will try to do this according with your advice.

I will check and let you know how things are going.

Best Regards,

Karolina

[edwin]

if they are in the same table then you can precalculate the status, yo can load your matrix as a table and then left join it to your table, this will give you your status as a field.  you wont need your IF statement anymore which would make it faster and simpler to maintain