Multi-Row Tool Usage Enhancement

[knnwndlm]

Hi SME,

I have built a one-year backward/forward calculation using the Multi-Row Formula tool shown in column L in the attached file. The results from the calculation are shown in column F. 

Now, I want to build a two-year backward/forward calculation using an enhanced version of the Multi-Row Formula earlier.  However, my formula got very complicated very quickly due to too many conditions imposed on the calculation. Now, I have four cells of data to consider, so you can imagine how unmanageable my Multi-Row Formula can be.

Any suggestions on a better way to handle this?

Thanks,

kwl

[binu_acs]

@knnwndlm here is the simplified version of your formula

IF [YEAR]-1 = [Row+1:YEAR] AND [YEAR]+1 = [Row-1:YEAR] THEN [Row-1:AMT] + [Row+1:AMT]
ELSEIF [YEAR]-1 = [Row+1:YEAR] THEN [Row+1:AMT]
ELSEIF [YEAR]+1 = [Row-1:YEAR] THEN [Row-1:AMT]
ELSE 0
ENDIF

[knnwndlm]

@binu_acs - Could you please help me with the 2 year backward/forward?  Those get harrowing with compounded conditions.

[PangHC]

You may just use lot of if 1 by 1. 

iif([Row-2:YEAR]=[Year]+2,[Row-2: AMT ],0)+
iif([Row-1:YEAR]=[Year]+1 or [Row-1:YEAR]=[Year]+2,[Row-1: AMT ],0)+
iif([Row+1:YEAR]=[Year]-1 or [Row-1:YEAR]=[Year]-2,[Row+1: AMT ],0)+
iif([Row+2:YEAR]=[Year]-2,[Row+2: AMT ],0)

[knnwndlm]

@PangHC - Thank you for your help!  Could you please help me understand why we would need the expression after the OR?  I'm a bit confused by +2/-1 on the second and third rows. Is this to ensure that you've captured repetitive years that fall within the 2 years?

[knnwndlm]

@PangHC - I got null for everything.  Not sure if this has anything to do with my data type for YEAR as Double.  Any suggestions?

[knnwndlm]

@PangHC - Please ignore the null situation.  Using the Data Cleansing seemed to have solved the trick.  However, I couldn't the same results.  Could you please take a look and see what I had missed?

[PangHC · Accepted solution]

@knnwndlm 
1. the sort issue, it was built for year by descending

2. the formula is wrong. where reverse on below red font.

IIF([Row-2:YEAR]=[YEAR]+2,[Row-2:AMT],0)+
IIF([Row-1:YEAR]=[YEAR]+1 OR [Row+1:YEAR]=[YEAR]+2,[Row-1:AMT],0)+
IIF([Row+1:YEAR]=[YEAR]-1 OR [Row-1:YEAR]=[YEAR]-2,[Row+1:AMT],0)+
IIF([Row+2:YEAR]=[YEAR]-2,[Row+2:AMT],0)

so,

1. add sort for sort year by descending.

2. fix the formula. 

IIF([Row-2:YEAR]=[YEAR]+2,[Row-2:AMT],0)+
IIF([Row-1:YEAR]=[YEAR]+1 OR [Row-1:YEAR]=[YEAR]+2,[Row-1:AMT],0)+
IIF([Row+1:YEAR]=[YEAR]-1 OR [Row+1:YEAR]=[YEAR]-2,[Row+1:AMT],0)+
IIF([Row+2:YEAR]=[YEAR]-2,[Row+2:AMT],0)

for +/- 1 rows, it may skip 1 year, where it +/- 2 year hence, it should take in consider. 

you can refer to the line 2017, where only have 2019. i only realise this for that year. 

actually you also can change all 4 rows to [year] is between -2 to +2 and not equal to it self. 

[knnwndlm]

@PangHC - Thank you much!