Multi-Row Tool Usage Enhancement

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

Sample Data.xlsx

Transformation

Accepted answers

AncientPandaman

@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.

All comments

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.

AncientPandaman

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?

Sample Data Workflow.yxzp

AncientPandaman

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

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

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!

knnwndlm

@PangHC - The Multi-Row Formula doesn't work for any records that have more than 2 year repetition. Since I need to make this dynamic, any suggestions of how I can go about doing this?

Sample Data.xlsx

knnwndlm

@PangHC - Think I figured it out. The formula I ended up using is:

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

AncientPandaman

@knnwndlm the formula is correct. but i suggest to use a summary to sum the total first. to remove the duplicate.
you can add a formula to remove the negative. where it can easy apply to even 100 rows for a year.

example.yxmd

knnwndlm

@PangHC - The line items with the same year are not dups. They came about because of the additional fields that I needed. In any case, I was able to resolve this using a Batch macro. All good now. Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703