Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
4 months ago
Solved

Nested IF

Hi to all can anyone help me on my calculated column. Basically im not sure how to explain it well but the formula in excel is like this RESULT=IF(A2="Article",IF(C2<0,IF(B2>0,1,0),0),IFERROR(D2...
Need Help
  • MFelix's avatar
    MFelix
    4 months ago

    Hi AllanBerces ,

     

    If you need to have it on a calculated column and the errors are are blank values then just use this formula:

     

    Result =
         IF('Table'[ART/OBJ] = "Article" ,
		 IF ('Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 ,
			IF( 'Table'[OBJECTREQUIRED] > 0 , 1, 0), 0), 
				IF(ISBLANK('Table'[O/S AS PER STATUS]),1, 'Table'[O/S AS PER STATUS])
)

     

    Or

    Result =
SWITCH(TRUE(),
    'Table'[ART/OBJ] = "Article" && 'Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 && 'Table'[OBJECTREQUIRED] > 0 ,1,
      ISBLANK('Table'[O/S AS PER STATUS]), 1,
           'Table'[O/S AS PER STATUS]
	)

     

     

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
4 months ago

Hi MFelix thank you for the reply, but can i have it in calculated column.i'm doing it on the calculated column. The #N/A shows blank on my calculated column

MFelix's avatar
MFelix
Icon for Super User rankSuper User
4 months ago

Hi AllanBerces ,

 

If you need to have it on a calculated column and the errors are are blank values then just use this formula:

 

Result =
         IF('Table'[ART/OBJ] = "Article" ,
		 IF ('Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 ,
			IF( 'Table'[OBJECTREQUIRED] > 0 , 1, 0), 0), 
				IF(ISBLANK('Table'[O/S AS PER STATUS]),1, 'Table'[O/S AS PER STATUS])
)

 

Or

Result =
SWITCH(TRUE(),
    'Table'[ART/OBJ] = "Article" && 'Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 && 'Table'[OBJECTREQUIRED] > 0 ,1,
      ISBLANK('Table'[O/S AS PER STATUS]), 1,
           'Table'[O/S AS PER STATUS]
	)