What can be the underlying formula for column C if column A and B are my inputs ?

Question

What can be the underlying formula for column C if column A and B are my inputs ?

My desired logic is
if any one of A or B is NA, C should return NA,
then if both A and B are numbers, C should return 0,
and if A is a number and B is Notpresent, C should be 1

and if B is a number and A is Notpresent, C should be -1.

Can anyone help me with this formula ? Below are the formulae i tried and it seems my approach is not working out.
=if(or(G3="",H3=""),"",ifs(H3="-NA-",1,G3="-NA-",-1,if(and(G3>0,H3>0),0,""))))

=IF(AND(ISNUMBER(A1), ISNUMBER(B1)), A1 - B1, IF(ISNUMBER(A1), A1, IF(ISNUMBER(B1), B1, "")))

=ifs(or(G3="",H3=""),"",H3="-NA-",1,G3="-NA-",-1,if(and(G3>0,H3>0),"0",""))

=IFS(OR(G6="-NA-",H6="-NA-"),"NA",H6="Not present" & ISNUMBER(G6),1,G6="Not present"&ISNUMBER(H6),-1,ISNUMBER(G6) & ISNUMBER(H6),"0")

=IFS(OR(G3="-NA-",H3="-NA-"),"NA",IF(AND(H3="Notpresent", ISNUMBER(G3)),1,""),IF(AND(G3="Notpresent", ISNUMBER(H3)),-1,""), if(and(ISNUMBER(G3), ISNUMBER(H3)),"0",""))

Please help me move out of this road block.

Thanks in advance

Kalyan.

Answer 1

Try another formula:

=IFS(OR(A1="-NA-",B1="-NA-"),"-NA-",AND(ISNUMBER(A1),ISNUMBER(B1)),0,AND(ISNUMBER(A1),B1="Notpresent"),1,AND(A1="Notpresent",ISNUMBER(B1)),-1,TRUE,"")