Formula duplication with different columns

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

Help

Accepted answers

CharlieS

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.

20190412-TransformForFormula.yxmd

All comments

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

Formula query.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703