IF, AND & OR statement

[nirkisna65]

I was trying to build the process using the following formulae from excel, I need help with the below.

=IF(AND(L2="Current",OR(P2=0,S2=0)),"Threshold issue: Asset Current, confirm Prior Year AV and Cost to ensure no Threshold Issue",IF(OR(L2="NewAcq",L2="PrevUnrpt"),"No Issues: Initial Year Reported",IF(AND(L2="MovedIn",P2=0,S2=0),"No Issues: Initial Year Reported in this Jurisdiction",IF(AND(L2="MovedIn",OR(P2>0,P2<0,S2>0,S2<0)),"Threshold issue: Asset moved in, confirm Prior Year AV and Cost to ensure no Threshold Issue",IF(R2>(S2+2),IF(O2>P2,"No Issues: Asset Cost Adjustment","Threshold Issue: Increase AV from PY but cost =< PY cost"),"No Issue")))))

Formula-Explanation	if Status ( Coloumn L)=Current, PY Reported Cost ( Column P) and 2020 Prior Year AV( Column S) =0
if Status ( Coloumn L)=New Acq or PrevUnrpt.
if Status ( Coloumn L)=Moved In, PY Reported Cost ( Column P) and 2020 Prior Year AV( Column S) =0
if Status ( Coloumn L)=Moved In, PY Reported Cost ( Column P) and 2020 Prior Year AV( Column S) = Greaterthan or Lessthan '0'
If CY Assessor's AV( Column R) is greatherthan 2020 Prior year AV( Column S) within 2 digits and if CY Rendered value( Column Q) is Greaterthan PY Reported Cost( Column P)
If CY Assessor's AV( Column R) is greatherthan 2020 Prior year AV( Column S) within 2 digits and if CY Rendered value( Column Q) is Lessthan PY Reported Cost( Column P)
Output	Threshold issue: Asset Current, confirm Prior Year AV and Cost to ensure no Threshold Issue
No Issues: Initial Year Reported
No Issues: Initial Year Reported in this Jurisdiction
Threshold issue: Asset moved in, confirm Prior Year AV and Cost to ensure no Threshold Issue
No Issues: Asset Cost Adjustment
Threshold Issue: Increase AV from PY but cost =< PY cost

Another formulae I need help with is with similar conditions. 

=IF(AD2="N","",IF(AA2="No Issues: Exempt","Exempt",IF(AA2="No Issues",IF(RIGHT(Z2,8)="expected","Depreciation as expected",IF(AND(LEFT(Y2,2)="No",LEN(Y2)>10),RIGHT(Y2,LEN(Y2)-11),IF(AND(LEN(AB2)>10,LEFT(AB2,2)="No"),RIGHT(AB2,LEN(AB2)-10),""))),"")))

If Calculated Status( Column AD)= N or Blank and if Column AA= NO Issues: Exempt

If Calculated Status( Column AD)= N or Blank and if Column AA= No Issues and Column Z=Threshold Issue: Depreciation as expected

If Calculated Status( Column AD)= N or Blank and if Column Y, Z, AA, AB= No Issues

Exempt

Depreciation as expected

Depreciation as expected

[cs11 · Accepted solution]

This might work; if you are still seeking an answer then perhaps you could try this with some of your data, and if it is not quite right then we can tweak it to get it there. This is pretty much a direct conversion into If Then Else statements, though I would bet there is a better way to do this through a Switch() command since it is like a case statement, but anyway, this might be one solution.

[nirkisna65]

Thanks for your help with this. 

All the IF statements should go into one column, as one formulae - 

I made couple of tweaks - 

1. updated all the [Output] to "No Issues", following the excel formulae, not if that is causing an error.

2. Selected the column in the output dropdown, where this formulae should flow combined.

Currently throws the Parse error.

[atcodedog05]

Hi @nirkisna65 ,

Can you attach the workflow.

[nirkisna65]

Please find attached.

[atcodedog05]

Hi @nirkisna65 ,

Can you provide the input files.

Without that its hard to run and check the output

[atcodedog05]

Hi @nirkisna65 ,

Try this workflow now it might work.

[nirkisna65]

The workflow runs with out error, could you please share your feedback or correction made for learning purpose. 

Also, I will test this workflow with other set of data to see if that works.

[atcodedog05]

Sure @nirkisna65 

Your last formula was missing EndIF

and your 2 columns [CY Assessor's AV] [2020 Prior Year AV] should have been number but it was a string. I changed them in the select tool.

Hope this helps.

[atcodedog05]

Hi @nirkisna65 ,

If my workflow and suggestion helped you please accept the post as solution.

Thank you

Cheers and Happy Analyzing 🙂