Formula_Put Number Under Particular Entity

Please see the attached two files: File 1 is the original file and file 2 is what we want. For Alabama, we just need to put app under 1020 and 1047 and the else are zero; for Arkansas, we just need to put app under 1020 and 1048 and the else are zero. I'm wondering what kind of formula we should write. Appreciate your time! Thanks!

Put App Under Particular Entities.xlsx

Common Use Cases

Accepted answers

fmvizcaino

Hi @Hongli1216 ,

You can create a IF condition to get that result.

if [Jurisdiction]='Alabama' and ([CoCd]=1020 or [CoCd]=1047)
then [App]
elseif [Jurisdiction]='Arkansas' and ([CoCd]=1020 or [CoCd]=1048)
then [App]
else 0
endif

Best,

Fernando Vizcaino

2020-09-09 Formula_Put Number Under Particular Entity.yxmd

Jonathan-Sherman

Hi @Hongli1216,

You could use the following formula:

IF ([Jurisdiction] = 'Alabama' AND [CoCd] IN(1020,1047))
OR ([Jurisdiction] = 'Arkansas' AND [CoCd] IN(1020,1048))
OR ([Jurisdiction] = 'Georgia' AND [CoCd] IN(1048))
OR ([Filing Method] = 'Con')
THEN [App]
ELSE 0
ENDIF

If you want to add further jurisdictions you can simply add a new line in the formula as follows (4th line as an example):

IF ([Jurisdiction] = 'Alabama' AND [CoCd] IN(1020,1047))
OR ([Jurisdiction] = 'Arkansas' AND [CoCd] IN(1020,1048))
OR ([Jurisdiction] = 'Georgia' AND [CoCd] IN(1048))
OR ([Jurisdiction] = 'Jurisdiction' AND [CoCd] IN(Values))
OR ([Filing Method] = 'Con')
THEN [App]
ELSE 0
ENDIF

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200909 - AppValues.yxmd

AbhilashR

Hi @Hongli1216, below is the modified logic to suit the additional scenarios.

IF [Jurisdiction] = 'Alabama'
	AND [CoCd] = 1020 or [CoCd] = 1047
THEN [App]
ELSEIF [Jurisdiction] = 'Arkansas'
	AND [CoCd] = 1020 or [CoCd] = 1048
THEN [App]
ELSEIF [Jurisdiction] = 'Georgia'
	AND [CoCd] = 1048
THEN [App]
ELSEIF [Jurisdiction] = 'Wisconsin'
	AND [CoCd] = 1000 
THEN [App]
ELSE 0
ENDIF

All comments

fmvizcaino

Hi @Hongli1216 ,

You can create a IF condition to get that result.

if [Jurisdiction]='Alabama' and ([CoCd]=1020 or [CoCd]=1047)
then [App]
elseif [Jurisdiction]='Arkansas' and ([CoCd]=1020 or [CoCd]=1048)
then [App]
else 0
endif

Best,

Fernando Vizcaino

2020-09-09 Formula_Put Number Under Particular Entity.yxmd

AbhilashR

Hi @Hongli1216, the following formula might help you achieve your output:

IF [Jurisdiction] = 'Alabama'
	AND [CoCd] = 1020 or [CoCd] = 1047
THEN [App]
ELSEIF [Jurisdiction] = 'Arkansas'
	AND [CoCd] = 1020 or [CoCd] = 1048
THEN [App]
ELSE 0
ENDIF

sample.yxmd

Hongli1216

Hi Fernando,

Thanks for your solution! Please see the attached file, actually I want to keep WI's app filed as Con. If there is no filing method as Con, your formula works perfectly. I have more than two states as Sep filing method, can I just put the rest of them after Arkansas? Please advise! Thanks!

Put App Under Particular Entities.xlsx

Hongli1216

Hi AbhilashR,

Thanks for your quick response! Actually there is two different filing method attached with more than two states. When we write formula for Sep filing but we still want to keep Con filing's app. Please advise! Appreciated your time!

Put App Under Particular Entities.xlsx

Jonathan-Sherman

Hi @Hongli1216,

You could use the following formula:

IF ([Jurisdiction] = 'Alabama' AND [CoCd] IN(1020,1047))
OR ([Jurisdiction] = 'Arkansas' AND [CoCd] IN(1020,1048))
OR ([Jurisdiction] = 'Georgia' AND [CoCd] IN(1048))
OR ([Filing Method] = 'Con')
THEN [App]
ELSE 0
ENDIF

If you want to add further jurisdictions you can simply add a new line in the formula as follows (4th line as an example):

IF ([Jurisdiction] = 'Alabama' AND [CoCd] IN(1020,1047))
OR ([Jurisdiction] = 'Arkansas' AND [CoCd] IN(1020,1048))
OR ([Jurisdiction] = 'Georgia' AND [CoCd] IN(1048))
OR ([Jurisdiction] = 'Jurisdiction' AND [CoCd] IN(Values))
OR ([Filing Method] = 'Con')
THEN [App]
ELSE 0
ENDIF

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200909 - AppValues.yxmd

Hongli1216

Hi Jonathan,

I like your formula very much! Thanks for your great help! There was some interesting things: in the front of your formula I had another formula, IF [CoCd]="1091" THEN [App] ELSEIF [Sep & Con] ="Con" THEN 0 ELSE [App] ENDIF, in order to make this formula work I had to choose V_String as CoCd's type but this type made your formula showing all App as Null; then I changed Int16 as CoCd's type which made your formula worked but my first formula showing Invalid type in operator==. I'm so confused about this. Please advise! Thanks for your time!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703