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,"")
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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,"")