Writing multiple formulas for each quarter

anamik72

Hello, I have simple formulas to write for each quarter for 3 years - so 12 formulas (12 new columns).

And then need to do the same for 7 different line items. Thus 84 formulas (and new columns).

Is there a way to do it more intelligently?

See below where I multiplied "1GR" rate times "0UC" volume for each quarter.

Similarly I need to do the same for "2DF" rate times "0UC" for each quarter and so on.

Can you help automate this instead of creating 84 manual formulas for 84 new columns? Thanks

Workflow

Accepted answers

Morgan_Thomas

I've attached what I've come up with. Start by transposing both input tables, grouping by Rank and Rank Name. Then I joined the data based on Rank, Rank Name, and a dynamic Year column. Then multiply and dynamically generate a name (i guessed the naming convention - hoping its right) then cross tab back out. The final select is where i reordered the columns to be grouped together - when you introduce more years you may need to reorder those in there. Im not sure if there is a way to dynamically do that.

Hope this helps 🙂

Writing mult formulas example v2.yxmd

All comments

grossal

Hi @anamik72,

are you able to provide a data sample and a desired output?

I think the solution will involve the usage of Transpose and Cross-Tab. These help to be more dynamic in these kind of scenarios.

Best

Alex

Maskell_Rascal

Hey @anamik72

One solution would be to use a couple Multi-Field Formula tools to write all your formulas, and then the Dynamic Rename tool to get the correct Field Names. Not sure if this is exactly what you had in mind, but could be a possible solution for you.

Let me know if this works for you.

Thanks!

Phil

Multiformula Workflow.yxmd

anamik72

Please find attached sample data. Thank you so much for your help.

Sample data for multiple formulas.xlsx

Morgan_Thomas

I had a similar thought to use transpose/cross tab and I've attached an example of what I came up with. I'm not sure if this is how your data is set up, so if you'd like to provide an example of that it would be helpful! but hope this helps:

Writing mult formulas example.yxmd

Morgan_Thomas

Hope this helps 🙂

Writing mult formulas example v2.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280