Multi Row inquiry

[Mo1995]

I have the following table

Each plan has 2 lines (Beg & End sub) I Want to get the average for each month

so I end up with a total of 5 columns, the fifth column shows the Average for each plan for each month

I'm confused which formula to use

Fos clarification I want for plan 12 PT, Jan 2020 to add beg+end and then divide by 2 and the result shown as the average and repeat onwards.

[Jean-Balteryx]

Hi @Mo1995 ,

Can you share sample data ?

[Mo1995]

HYG

[Jean-Balteryx · Accepted solution]

I crosstab the data to get one column for beginning and one for ending then divide the sum of them by two in a new column. Here it is !

[IraWatt]

This workflow should achieve what you want (attached below):

Use a summarize tool grouping by MM-YYYY then taking an average:

Then join the output with the data using a join tool based on MM-YYYY:

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Ira

[atcodedog05]

Hi @Mo1995 

My take on this using multi-row formula tool.

Workflow:

Hope this helps : )

[Mo1995]

Thanks alot !

worked like a charm

[Jean-Balteryx]

You are welcome @Mo1995 !