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

TEMP.xlsx

Yoshiro_Fujimori

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

1264432.yxmd