How to dynamically create formulas based on field names

I have one row of data and I need to create a series of calculations based on the field names. Looking at the screenshot, the values provided are [CumPD - 12] and all [df#] fields.

How do I keep creating formulas like the ones in the screenshot without having to it do it manually? I need to keep creating these formulas from [CumPD - 13] to [CumPD - 300] and will be a hassle to do it manually. The pattern remains consistent as shown.

Thank you!

example.PNG

Workflow

Transformation

Accepted answers

apathetichell

Maybe this makes more sense with the actual values. The column I'm creating does use that formula.

ratio anaylsis df to cumulative.yxzp

All comments

apathetichell

transpose - dynamically create a match logic. self join the two matched fields - this reduce your formula to value 1/value 2.

overhead_press

Appreciate the response, any chance you could show me with this example dataset?

example.xlsx

apathetichell

I'm not sure how your formula extract would work with your sample data. As I'm seeing it you'd have something like

(15/11)/12 for CUM PD-13 and

((15/11)/12)/13 for CUM PD-14

Is that the accurate formula you would expect?

overhead_press

the calculations are separate per identifier, so for 11111 Cum PD - 13 it would be (11/1) and for 222222 it would be (15/11)

apathetichell

Hi - yup. got that. so for 22222 for CUM PD-14 you would expect 15/11/12/13 - according to your formula. I do not believe that makes any sense give your sample data.

overhead_press

for 22222 for CUM PD-14 it would be [CumPD - 13] / [df14] so (15/11/12)

The formula is:

[CumPD - X] = [CumPD - X-1] / [dfX]

apathetichell

Maybe this makes more sense with the actual values. The column I'm creating does use that formula.

ratio anaylsis df to cumulative.yxzp

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703