Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago
Solved

Need help on excel conditional formula

Hi  I have need to apply excel formula  IF(ISBLANK([@Lookup])=FALSE,"MIS-2",IF(AND([@MIGO]<0,ISBLANK([@MIRO])=TRUE),"MIS-5",IF(AND([@MIGO]>0,ISBLANK([@MIRO])=TRUE),"MIS-1",IF(AND([@MIRO]<>0,ISBLANK...
Need Help
  • mlsx4's avatar
    mlsx4
    2 years ago

    Hi KuntalSingh 

     

    Data is not well inserted so I can't reproduce your case. I'm not sure when you need MIS-2. But, with the data I have understood, you can do:

     

     

    = Table.AddColumn(#"Tipo cambiado", "Personalizado", each if [MIGO]<0 and [MIRO]=null then "MIS-5" else 
  if [MIGO]>0 and [MIRO]=null then "MIS-1" else
  if [MIGO]=null and [MIRO]<>null then "MIS-4" else
  if [MIGO]<>null and [MIRO]<>null then "MIS-3" else "MIS-2")

     

     

mlsx4's avatar
mlsx4
Memorable Member
2 years ago

Hi KuntalSingh 

 

Data is not well inserted so I can't reproduce your case. I'm not sure when you need MIS-2. But, with the data I have understood, you can do:

 

 

= Table.AddColumn(#"Tipo cambiado", "Personalizado", each if [MIGO]<0 and [MIRO]=null then "MIS-5" else 
  if [MIGO]>0 and [MIRO]=null then "MIS-1" else
  if [MIGO]=null and [MIRO]<>null then "MIS-4" else
  if [MIGO]<>null and [MIRO]<>null then "MIS-3" else "MIS-2")

 

 

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago

Thanks for your reply

I already used the same formula.

 

= Table.AddColumn(#"Filtered Rows", "MIS", each if[Lookup]<>null then "MIS2" else if [MIGO]<0 and [MIRO]=null then "MIS5" else if [MIGO]>0 and [MIRO]=null then "MIS1" else if [MIRO]=null and [MIGO]<>null then "MIS4" else if [MIRO]<> null and [MIGO]<>null then "MIS3" else "")