Calculations for selected rows across multiple columns?

[msoysal]

Hello,

I am looking for help to utilize multi field / aggregation tools that perform calculations for selected rows across multiple columns.

Category	Grade	1	2	3	4	5	6	7	8	9	10	11	12
Medium	X	85	90	104	102	109	100	110	103	101	95	96	102
Small	X	79	92	106	98	99	93	111	112	110	106	98	96
Medium	A	84	82	120	73	138	122	84	137	132	105	84	63
Medium	B	102	96	106	86	95	93	84	83	140	123	109	108
Small	A	87	87	63	90	110	95	110	120	115	110	106	95
Small	C	75	80	100	114	115	113	115	106	97	87	88	102

In the table above, top 2 rows with grades X are the benchmark numbers of the 3 categories for 12 months. I want to average each grade (A, B, A and C) with the corresponding benchmark grade for each category. That is, there are total of 4 averages to be calculated for each month;                                                                                                                                                                    

1            Medium-A and Medium-X                                                                                                                                                         

2            Medium-B and Medium-X                                                                                                                                                          

3            Small-A and Small-X                                                                                                                                                       

4            Small-C and Small-X                                                                                                                                                       

I found several solutions such as pairing them up using filters or record IDs then calculating average, and union but, my solutions are too manual for my actual data set that is way larger then shown above.

Your help is much appreciated!

Thank you                                                                                 

[KOBoyle]

Can you provide a table or screenshot with the output that you need for the example you provided?

[ponraj]

Here is the sample workflow for your case if I understand your description correctly. Hope this is helpful. 

WorkflowResults

[msoysal]

Thank you for taking time and providing a solution. This is a great solution, and it works perfectly. But I was actually looking for usage of multi field tool in select row operations to shorten the workflow. For example, when I pair say, Medium-X and Medium-A row wise, how to average the columns across.

		1	2	3	4	5	6	7	8	9	10	11	12
Medium	X	85	90	104	102	109	100	110	103	101	95	96	102
Medium	A	84	82	120	73	138	122	84	137	132	105	84	63

Thanks

[msoysal]

Thank you for looking in to this. The output cold be as simple as shown below. Or even simpler with no string field changes. and the numeric field values to show averages.

		1	2	3	4	5	6	7	8	9	10	11	12
Medium	A_Avg	84.5	86	112	87.5	123.5	111	97	120	116.5	100	90	82.5
Medium	B_Avg	93.5	93	105	94	102	96.5	97	93	120.5	109	102.5	105
Small	A_Avg	83	89.5	84.5	94	104.5	94	110.5	116	112.5	108	102	95.5
Small	C_Avg	77	86	103	106	107	103	113	109	103.5	96.5	93	99

Thanks

[ponraj · Accepted solution]

Here is the sample workflow with multifield tool you are looking for.  Hope this is helpful. 

ResultsWorkflow

[msoysal]

Brilliant!

Thank you so much.