Nested IF

[AllanBerces]

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,1))

OUTPUT

ART/OBJ	OBJECTREQUIRED	STOCK DEV. (AGAINST RCVD STOCK)	O/S AS PER STATUS	RESULT
Article	0.7	-0.7	#N/A	1
Object	0	#N/A	#N/A	1
Object	0	#N/A	#N/A	1
Object	0	#N/A	#N/A	1
Object	0	#N/A	#N/A	1
Article	18	-18	#N/A	1
Object	0	#N/A	#N/A	1
Object	0	#N/A	#N/A	1
Article	0	0	0	0
Article	0	4	0	0
Article	0	0	0	0
Article	1	1.68	0	0
Object	0	#N/A	0	0
Object	0	#N/A	0	0
Object	0	#N/A	0	0

[V-yubandi-msft]

Hi @AllanBerces ,

When you have a moment, please review the latest response from @MFelix  and let us know if it meets your expectations. If you need any further information or clarification, please let us know.

Thanks for your input @MFelix .

Regards,
Yugandhar.

[MFelix]

Hi @AllanBerces ,

If you are getting this information from the Excel file be carefull because the #N/A will not load the data properly and you will get an error on the cells like below:

This will make your power query not to load all rows so you also need to handle the errors, however in your formula you are also not considering if there is an error on cell C3 so this can mess up your calculation because if you have an article with C column error then you will get an error on the final colum try the following code on Power Query:

try if [#"ART/OBJ"] = "Article" then 
      
      if [#"STOCK DEV. (AGAINST RCVD STOCK)"] <0 then 
        1 else       
        
        if [OBJECTREQUIRED] > 0 then 1 else 0


         else [#"O/S AS PER STATUS"]

         otherwise 1

You should also handle those errors doing the replacment of values:

 Table.ReplaceErrorValues(#"Personalizado Adicionado", {{"O/S AS PER STATUS", 0}})

Where there is Zero just place whatever value you think is correct.

[AllanBerces]

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 · Accepted solution]

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]

Hi @MFelix @V-yubandi-msft thnak you very much working good