topic Re: Line by Line execution of interdependent columns in Alteryx Designer Desktop Discussions

Line by Line execution of interdependent columns

Sonali_25 — Wed, 19 Jun 2019 06:37:21 GMT

Hi,

I have an excel file where I need to create two columns. Each column has formulas that are interdependent on the other column.

The excel file looks like this -

Payment No.	PIK Interest	PIK Accrual	Ending Principle
-	-	-	110000000
32	4%	391111	110391111
90	4%
90	4%
90	4%
90	4%
90	4%
90	4%
90	4%
90	4%

"PIK Accrual" Column is calculated as (110000000*0.04*(32/360)) which equals to 110000000 whereas,

"Ending Principle" Column is calculated as (391111+110000000) which equals to 110391111.

That is, to calculate "PIK Accrual", we need to have the prior row of "Ending Principle" populated and to calculate "Ending Principle", we need to have the active row of "PIK Accrual" populated.

Please Note : Only the starting value of "Ending Principle" (i.e., 110000000) is given in the data set as shown above.

I have attached the excel file with the formula for your reference.

Re: Line by Line execution of interdependent columns

afv2688 — Wed, 19 Jun 2019 10:07:03 GMT

Hello @Sonali_25 ,

Hope this helps. The way I solved it was using an iterative macro.

Cheers

Re: Line by Line execution of interdependent columns

JoeS — Wed, 19 Jun 2019 10:09:16 GMT

Hi @Sonali_25

I was just beaten to it by @afv2688 however, I went the more straight forward way (not necessarily any better) of two multi-row formulas.

Re: Line by Line execution of interdependent columns

Sonali_25 — Wed, 19 Jun 2019 11:49:57 GMT

Thank you so much @afv2688 and @JoeS for the quick help. Both the concepts are working absolutely fine.

Much Thanks !!