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 (S2+2),IF(O2>P2,"No Issues: Asset Cost Adjustment","Threshold Issue: Increase AV from PY but cost =< PY cost"),"No Issue"))))) Formula-Explanationif Status ( Coloumn L)=Current, PY Reported Cost ( Column P) and 2020 Prior Year AV( Column S) =0if 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) =0if 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)OutputThreshold issue: Asset Current, confirm Prior Year AV and Cost to ensure no Threshold IssueNo Issues: Initial Year ReportedNo Issues: Initial Year Reported in this JurisdictionThreshold issue: Asset moved in, confirm Prior Year AV and Cost to ensure no Threshold IssueNo Issues: Asset Cost AdjustmentThreshold Issue: Increase AV from PY but cost = 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: ExemptIf Calculated Status( Column AD)= N or Blank and if Column AA= No Issues and Column Z=Threshold Issue: Depreciation as expectedIf Calculated Status( Column AD)= N or Blank and if Column Y, Z, AA, AB= No Issues ExemptDepreciation as expectedDepreciation as expected

IF, AND & OR statement

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

Developer

Preparation

Accepted answers

cs11

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.

nirkisna.yxmd

nirkisna65

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.9R2),"Threshold Issue",IF(Q2>(1.1R2),"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")