Dynamic formula for multi-column analysis

[Anirudh_A]

Hi friends, 

I have the data which looks like the one above. I want to create the average but making it dynamic. The results are the right 3 columns - I also want to strip out the middle section from the column name and create a standard name of the new column. 

The empty cells show that data can be empty but any serial number will have data for the same Q_xx. 

Please help - I am tinkering around the multi-field formula tool but couldn't figure out

Thanks in advance

[Yoshiro_Fujimori · Accepted solution]

Hi @Anirudh_A 

I am not sure how you want to deal with "series B", but it should work for other series without much efforts.

Workflow

Output

Serial No.	A_Q_1	A_Q_2	A_Q_3
1	3.5	2.5
2		4.5	5.5
3			5.5
4	1	2	3
5	1.5		2

[Anirudh_A]

Thanks so much @Yoshiro_Fujimori 

It worked, had to play around with the formula a bit - but you helped me do the heavy stuff :D