NPV Formula Dynamic String or Columns for Formula

[Trich]

I am working on a Net Present Value calculation, but the number of columns change between sheets as well as the interest rates. That is why I didn't use a summary  Net Present Value because the rate changes between products and I need it to be dynamic. However my column names or time periods are not constant.

 

I used a summary tool to concatenate all the columns in to one field, but I do not know how to insert into formula. 

 

 Any help would be greatly appreciated.

[Inactive User]

I would highly recommend taking those unknown column headings and transposing them all using a RecordID grouping. Then you can do your calculations from a row-level structure rather than a column level structure. Creates more flexibility in processing and will overcome your issue.

[Trich]

It is a NPV calculation, but can't make rate variable in transposed calculation.

Get Outlook for Android

[Trich · Accepted solution]

I found a solution from this gentleman. I wrote the NPV formula out in XML!

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-do-dynamic-calculations-across-...