Formula duplication with different columns

[SarahJ1]

Hi

I've created a new column  titled 1920 Home Progression using the following formula:

(If [Sandwich Year] = "Y" Then [Right_1819 Intake Home Enrols] * 0.084 Else [Progression rate]*[1819 Intake Home Enrols] Endif
) + (If [Sandwich Year] = "Y" Then [Right_1819 Cont Home Enrols] * 0.084 Else [Progression rate]*[1819 Cont Home Enrols] Endif)

I now need to replicate the column replacing Home with EU then with Overseas

I then need to repeat for different years e.g. replacing 1819 with 1920 etc

Other than creating all the columns in the formula tool and copying and pasting the formula and replacing the relevant column names is there any other way to do this?

Thanks

[CharlieS]

Hi @SarahJ1 

The Multi-Field Formula tool is designed to apply the same formula to multiple fields. However, it doesn't sound like this will be effective for this case based on the description of your formulas/fields. I think some data transformation will be necessary to get the job done.

Could you share some sample data (in a Text Input in a workflow works best) that could be used for testing?

[SarahJ1]

Hi @CharlieS 

I've created a simpler example that uses the same principles

So can we create a 1819 OS column without copying and pasting.  And then replicate for 1920?

Thanks

Sarah

[CharlieS · Accepted solution]

Check out the attached example solution to see how a few transformations can really simplify your formulas. The first browse is after the calculations are complete and the second transforms those results back to the original format.

[SarahJ1]

Great thank you.  I've been able to use these priniciples to scale up the solution to get the desired result