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

[nirkisna65 · Accepted solution]

Thank you for the feedback it helped. 

Could you please let me know if the below listed formulae were correctly formulated in Alteryx - There are 4 formulae function in the workflow attached pertaining to each of the columns listed below. (Also find attached the copy of report, refer Col. X through AA)

Header	Column used in Alteryx	Excel Formulae
1	Status Check and Cost Adjustment if Rendered Cost > PY Cost when CYAV>PYAV	=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")))))
2	Depreciation as expected: CYAV is within the range of <= 20% of PYAV	= IF(S2="","No Issues", IF(R2<(0.8*S2),"Threshold Issue: Depreciation as expected",IF(R2<(S2-2),"No Issues: Depreciation as expected","No Issues")))
3	True if Rendered AV equals CY AV (or is within the range of 10% greater or 10% less than CYAV)	=IF(R2=0,IF(U2=TRUE, "No Issues: Exempt","Value Not Yet Entered"),IF(Q2<(0.9*R2),"Threshold Issue",IF(Q2>(1.1*R2),"Threshold Issue","No Issues")))
4	Residual Value Check: CYAV=PYAV and CY cost= PY costs and Residual Value = True	=IF(AND(R2<(S2+3),R2>(S2-3)), IF(O2=P2,IF(V2=TRUE,"No Issues: Residual Value","Confirm at Residual Value"),"Threshold Issue: CYAV=PYAV but cost does not equal PY cost"),"No Issue")