Score Values Across Multiple Tests

[covareo]

I have a spreadsheet of test results for 5 test subjects (bases) across 20 tests (configs). Each test has 40 columns of data - I've attached a sample of the data below that shows all 5 test bases for 10 test Configs and 10 columns of data.

I am trying to count the number of times in each column that a given base (T0, T1, T2, T3, T4) scores the lowest number for E thru J and N, The highest number for L and M, and K/Letter Grade A best and F worst. Ties can each score 1 point.

wp-testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Group	Config	Weight	Base	PGT	PMU	DBQT	Q	SC	ST	Grade	PERF	STRU	LCP
2	1	default	1	T0							A	100%	100%	679
3	1	default	1	T1							A	100%	100%	628
4	1	default	1	T2							A	100%	100%	563
5	1	default	1	T3							A	99%	98%	829
6	1	default	1	T4							A	100%	100%	531
7	2	QM	1	T4	0.0384	5,347	0.0009	24	7	7	A	100%	100%	508
8	2	QM	1	T2	0.5073	7,832	0.0026	33	9	6	A	100%	100%	552
9	2	QM	1	T3	0.5665	8,140	0.0019	32	12	8	A	99%	98%	678
10	2	QM	1	T0	0.0596	8,966	0.0021	39	9	7	A	99%	100%	739
11	2	QM	1	T1	0.0461	8,975	0.0013	39	9	7	A	99%	99%	724
12	3	G	1	T0	0.0829	6,701	0.0021	33	9	8	A	100%	100%	614
13	3	G	1	T2	0.0672	6,941	0.0013	38	9	6	A	100%	100%	581
14	3	G	1	T4	0.5984	10,061	0.0025	29	7	7	A	100%	100%	542
15	3	G	1	T1	0.1207	10,220	0.0018	44	9	7	A	100%	100%	684
16	3	G	1	T3	0.6263	10,507	0.0015	37	12	8	A	99%	98%	721
17	4	WPF	1	T2	0.7910	1,324	0.0034	47	9	7	A	99%	100%	710
18	4	WPF	1	T0	0.2219	7,295	0.0024	42	9	9	A	98%	100%	863
19	4	WPF	1	T3	0.6173	10,453	0.0290	43	12	9	A	97%	97%	1000
20	4	WPF	1	T1	0.1896	10,700	0.0028	53	9	9	A	99%	100%	736
21	4	WPF	1	T4	0.0477	7,090	0.0100	38	7	8	A	100%	100%	629
22	5	E	2	T2	0.9757	1,535	0.0066	54	26	8	A	100%	100%	531
23	5	E	2	T0	0.7157	11,305	0.0034	49	26	10	A	100%	100%	658
24	5	E	2	T3	0.5705	11,774	0.0020	53	29	10	A	98%	98%	832
25	5	E	2	T1	0.7034	15,096	0.0069	63	26	9	A	100%	100%	656
26	5	E	2	T4	0.0723	9,396	0.0013	48	25	12	A	100%	99%	652
27	6	EP	3	T2	0.0710	10,513	0.0020	68	27	8	A	100%	100%	640
28	6	EP	3	T3	0.0824	10,729	0.0021	67	30	10	A	98%	98%	954
29	6	EP	3	T0	0.7965	13,516	0.0129	70	27	10	A	100%	100%	645
30	6	EP	3	T1	0.7848	17,275	0.0056	73	27	9	A	100%	100%	535
31	6	EP	3	T4	0.0723	11,180	0.0018	62	26	12	A	100%	99%	646
32	7	C	4	T0	0.0596	9,242	0.0015	46	9	8	A	100%	100%	580
33	7	C	4	T2	0.0609	9,489	0.0016	48	9	6	A	100%	100%	700
34	7	C	4	T3	0.0647	9,644	0.0027	47	12	8	A	97%	97%	937
35	7	C	4	T1	0.0720	12,839	0.0017	54	9	7	A	100%	100%	563
36	7	C	4	T4	0.0533	9,001	0.0012	39	7	7	A	100%	100%	612
37	8	GT	3	T4	0.0422	7,532	0.0011	35	9	8	A	100%	99%	578
38	8	GT	3	T0	0.0466	7,768	0.0013	42	11	9	A	99%	99%	747
39	8	GT	3	T2	0.0476	8,035	0.0015	44	11	7	A	100%	99%	561
40	8	GT	3	T3	0.0489	8,179	0.0020	44	14	9	A	98%	97%	901
41	8	GT	3	T1	0.0567	11,364	0.0015	50	11	8	A	100%	100%	605
42	9	BV	1	T4	0.0410	7,583	0.0012	29	7	7	A	100%	100%	493
43	9	BV	1	T0	0.0505	7,797	0.0023	36	9	8	A	100%	100%	608
44	9	BV	1	T3	0.1584	8,156	0.0024	37	12	8	A	98%	98%	874
45	9	BV	1	T2	0.0836	8,228	0.0016	38	9	6	A	100%	100%	508
46	9	BV	1	T1	0.0648	11,385	0.0032	44	9	7	A	100%	99%	664
47	10	A	5	T4	0.0372	7,107	0.0008	23	7	7	A	100%	100%	572
48	10	A	5	T0	0.0419	7,326	0.0010	30	9	8	A	99%	100%	711
49	10	A	5	T2	0.0452	7,527	0.0012	32	9	6	A	100%	100%	632
50	10	A	5	T3	0.0440	7,722	0.0010	31	12	8	A	99%	98%	774
51	10	A	5	T1	0.0537	10,954	0.0014	38	9	7	A	100%	99%	620
Help

How often did T2 get the lowest number for column E/PGT compared to T0-T4 for each Group/Config?

So a summary Table Might look like this:

wp-testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		PGT	PMU	DBQT	Q	SC	ST	Grade	PERF	STRU	LCP
2	T0	2	1	3	2	1	2	3	7	6	2
3	T1	0	1	0	1	1	1	3	7	6	1
4	T2	3	1	0	1	3	3	3	8	7	3
5	T3	1	3	2	0	1	0	0	0	0	0
6	T4	4	4	5	6	4	4	5	9	8	4
Summary

It would then be helpful to see the weighted scores where each Config that scores for a Base has that score multiplied by the weight in column C to allow importance across tests to stand out.

wp-testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		PGT	PMU	DBQT	Q	SC	ST	Grade	PERF	STRU	LCP
2	T0	8	2	6	6	5	5	3	12	6	2
3	T1	0	3	0	1	1	3	6	8	6	1
4	T2	9	1	0	1	3	9	3	8	7	3
5	T3	2	10	9	0	4	0	0	0	0	0
6	T4	16	12	20	24	12	10	20	12	8	4
Summary

So in summary: I'm trying to find the most efficient/fastest (lowest value) Base in each Config/Group and score a point for that Base, then total those points in a summary table.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[covareo]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I updated my account - thank you!

I am using Office 365 on Mac OS but I also have access to a PC if needed. That said most of my co-workers are Mac only so formulas would be more sharable than a macro (if thats part of the reason for asking)

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Group	Config	Weight	Base	PGT	PMU	DBQT	Q	SC	ST	Grade	PERF	STRU	LCP			PGT	PMU	DBQT	Q	SC	ST	Grade	PERF	STRU	LCP
2	1	default	1	T0							A	1	1	679		T0	0	1	1	0	0	0	0	2	0	0
3	1	default	1	T1							A	1	1	628		T1	0	0	0	0	0	0	0	1	0	2
4	1	default	1	T2							A	1	1	563		T2	2	3	1	0	0	9	0	0	0	2
5	1	default	1	T3							A	0.99	0.98	829		T3	0	0	0	0	0	0	0	10	10	0
6	1	default	1	T4							A	1	1	531		T4	7	5	7	9	9	0	0	0	0	6
7	2	QM	1	T4	0.0384	5347	0.0009	24	7	7	A	1	1	508
8	2	QM	1	T2	0.5073	7832	0.0026	33	9	6	A	1	1	552
9	2	QM	1	T3	0.5665	8140	0.0019	32	12	8	A	0.99	0.98	678
10	2	QM	1	T0	0.0596	8966	0.0021	39	9	7	A	0.99	1	739
11	2	QM	1	T1	0.0461	8975	0.0013	39	9	7	A	0.99	0.99	724
12	3	G	1	T0	0.0829	6701	0.0021	33	9	8	A	1	1	614
13	3	G	1	T2	0.0672	6941	0.0013	38	9	6	A	1	1	581
14	3	G	1	T4	0.5984	10061	0.0025	29	7	7	A	1	1	542
15	3	G	1	T1	0.1207	10220	0.0018	44	9	7	A	1	1	684
16	3	G	1	T3	0.6263	10507	0.0015	37	12	8	A	0.99	0.98	721
17	4	WPF	1	T2	0.791	1324	0.0034	47	9	7	A	0.99	1	710
18	4	WPF	1	T0	0.2219	7295	0.0024	42	9	9	A	0.98	1	863
19	4	WPF	1	T3	0.6173	10453	0.029	43	12	9	A	0.97	0.97	1000
20	4	WPF	1	T1	0.1896	10700	0.0028	53	9	9	A	0.99	1	736
21	4	WPF	1	T4	0.0477	7090	0.01	38	7	8	A	1	1	629
22	5	E	2	T2	0.9757	1535	0.0066	54	26	8	A	1	1	531
23	5	E	2	T0	0.7157	11305	0.0034	49	26	10	A	1	1	658
24	5	E	2	T3	0.5705	11774	0.002	53	29	10	A	0.98	0.98	832
25	5	E	2	T1	0.7034	15096	0.0069	63	26	9	A	1	1	656
26	5	E	2	T4	0.0723	9396	0.0013	48	25	12	A	1	0.99	652
27	6	EP	3	T2	0.071	10513	0.002	68	27	8	A	1	1	640
28	6	EP	3	T3	0.0824	10729	0.0021	67	30	10	A	0.98	0.98	954
29	6	EP	3	T0	0.7965	13516	0.0129	70	27	10	A	1	1	645
30	6	EP	3	T1	0.7848	17275	0.0056	73	27	9	A	1	1	535
31	6	EP	3	T4	0.0723	11180	0.0018	62	26	12	A	1	0.99	646
32	7	C	4	T0	0.0596	9242	0.0015	46	9	8	A	1	1	580
33	7	C	4	T2	0.0609	9489	0.0016	48	9	6	A	1	1	700
34	7	C	4	T3	0.0647	9644	0.0027	47	12	8	A	0.97	0.97	937
35	7	C	4	T1	0.072	12839	0.0017	54	9	7	A	1	1	563
36	7	C	4	T4	0.0533	9001	0.0012	39	7	7	A	1	1	612
37	8	GT	3	T4	0.0422	7532	0.0011	35	9	8	A	1	0.99	578
38	8	GT	3	T0	0.0466	7768	0.0013	42	11	9	A	0.99	0.99	747
39	8	GT	3	T2	0.0476	8035	0.0015	44	11	7	A	1	0.99	561
40	8	GT	3	T3	0.0489	8179	0.002	44	14	9	A	0.98	0.97	901
41	8	GT	3	T1	0.0567	11364	0.0015	50	11	8	A	1	1	605
42	9	BV	1	T4	0.041	7583	0.0012	29	7	7	A	1	1	493
43	9	BV	1	T0	0.0505	7797	0.0023	36	9	8	A	1	1	608
44	9	BV	1	T3	0.1584	8156	0.0024	37	12	8	A	0.98	0.98	874
45	9	BV	1	T2	0.0836	8228	0.0016	38	9	6	A	1	1	508
46	9	BV	1	T1	0.0648	11385	0.0032	44	9	7	A	1	0.99	664
47	10	A	5	T4	0.0372	7107	0.0008	23	7	7	A	1	1	572
48	10	A	5	T0	0.0419	7326	0.001	30	9	8	A	0.99	1	711
49	10	A	5	T2	0.0452	7527	0.0012	32	9	6	A	1	1	632
50	10	A	5	T3	0.044	7722	0.001	31	12	8	A	0.99	0.98	774
51	10	A	5	T1	0.0537	10954	0.0014	38	9	7	A	1	0.99	620
Data

Cell Formulas
Range	Formula
Q2:Z6	Q2	=MMULT(TRANSPOSE(($B$2:$B$51=$B$2:$B$51)*($D$2:$D$51=$P2)),(E$2:E$51<>"")*(E$2:E$51=MINIFS(E$2:E$51,$B$2:$B$51,$B$2:$B$51)))

 

[covareo]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Group	Config	Weight	Base	PGT	PMU	DBQT	Q	SC	ST	Grade	PERF	STRU	LCP			PGT	PMU	DBQT	Q	SC	ST	Grade	PERF	STRU	LCP
2	1	default	1	T0							A	1	1	679		T0	0	1	1	0	0	0	0	2	0	0
3	1	default	1	T1							A	1	1	628		T1	0	0	0	0	0	0	0	1	0	2
4	1	default	1	T2							A	1	1	563		T2	2	3	1	0	0	9	0	0	0	2
5	1	default	1	T3							A	0.99	0.98	829		T3	0	0	0	0	0	0	0	10	10	0
6	1	default	1	T4							A	1	1	531		T4	7	5	7	9	9	0	0	0	0	6
7	2	QM	1	T4	0.0384	5347	0.0009	24	7	7	A	1	1	508
8	2	QM	1	T2	0.5073	7832	0.0026	33	9	6	A	1	1	552
9	2	QM	1	T3	0.5665	8140	0.0019	32	12	8	A	0.99	0.98	678
10	2	QM	1	T0	0.0596	8966	0.0021	39	9	7	A	0.99	1	739
11	2	QM	1	T1	0.0461	8975	0.0013	39	9	7	A	0.99	0.99	724
12	3	G	1	T0	0.0829	6701	0.0021	33	9	8	A	1	1	614
13	3	G	1	T2	0.0672	6941	0.0013	38	9	6	A	1	1	581
14	3	G	1	T4	0.5984	10061	0.0025	29	7	7	A	1	1	542
15	3	G	1	T1	0.1207	10220	0.0018	44	9	7	A	1	1	684
16	3	G	1	T3	0.6263	10507	0.0015	37	12	8	A	0.99	0.98	721
17	4	WPF	1	T2	0.791	1324	0.0034	47	9	7	A	0.99	1	710
18	4	WPF	1	T0	0.2219	7295	0.0024	42	9	9	A	0.98	1	863
19	4	WPF	1	T3	0.6173	10453	0.029	43	12	9	A	0.97	0.97	1000
20	4	WPF	1	T1	0.1896	10700	0.0028	53	9	9	A	0.99	1	736
21	4	WPF	1	T4	0.0477	7090	0.01	38	7	8	A	1	1	629
22	5	E	2	T2	0.9757	1535	0.0066	54	26	8	A	1	1	531
23	5	E	2	T0	0.7157	11305	0.0034	49	26	10	A	1	1	658
24	5	E	2	T3	0.5705	11774	0.002	53	29	10	A	0.98	0.98	832
25	5	E	2	T1	0.7034	15096	0.0069	63	26	9	A	1	1	656
26	5	E	2	T4	0.0723	9396	0.0013	48	25	12	A	1	0.99	652
27	6	EP	3	T2	0.071	10513	0.002	68	27	8	A	1	1	640
28	6	EP	3	T3	0.0824	10729	0.0021	67	30	10	A	0.98	0.98	954
29	6	EP	3	T0	0.7965	13516	0.0129	70	27	10	A	1	1	645
30	6	EP	3	T1	0.7848	17275	0.0056	73	27	9	A	1	1	535
31	6	EP	3	T4	0.0723	11180	0.0018	62	26	12	A	1	0.99	646
32	7	C	4	T0	0.0596	9242	0.0015	46	9	8	A	1	1	580
33	7	C	4	T2	0.0609	9489	0.0016	48	9	6	A	1	1	700
34	7	C	4	T3	0.0647	9644	0.0027	47	12	8	A	0.97	0.97	937
35	7	C	4	T1	0.072	12839	0.0017	54	9	7	A	1	1	563
36	7	C	4	T4	0.0533	9001	0.0012	39	7	7	A	1	1	612
37	8	GT	3	T4	0.0422	7532	0.0011	35	9	8	A	1	0.99	578
38	8	GT	3	T0	0.0466	7768	0.0013	42	11	9	A	0.99	0.99	747
39	8	GT	3	T2	0.0476	8035	0.0015	44	11	7	A	1	0.99	561
40	8	GT	3	T3	0.0489	8179	0.002	44	14	9	A	0.98	0.97	901
41	8	GT	3	T1	0.0567	11364	0.0015	50	11	8	A	1	1	605
42	9	BV	1	T4	0.041	7583	0.0012	29	7	7	A	1	1	493
43	9	BV	1	T0	0.0505	7797	0.0023	36	9	8	A	1	1	608
44	9	BV	1	T3	0.1584	8156	0.0024	37	12	8	A	0.98	0.98	874
45	9	BV	1	T2	0.0836	8228	0.0016	38	9	6	A	1	1	508
46	9	BV	1	T1	0.0648	11385	0.0032	44	9	7	A	1	0.99	664
47	10	A	5	T4	0.0372	7107	0.0008	23	7	7	A	1	1	572
48	10	A	5	T0	0.0419	7326	0.001	30	9	8	A	0.99	1	711
49	10	A	5	T2	0.0452	7527	0.0012	32	9	6	A	1	1	632
50	10	A	5	T3	0.044	7722	0.001	31	12	8	A	0.99	0.98	774
51	10	A	5	T1	0.0537	10954	0.0014	38	9	7	A	1	0.99	620
Data

Cell Formulas
Range	Formula
Q2:Z6	Q2	=MMULT(TRANSPOSE(($B$2:$B$51=$B$2:$B$51)*($D$2:$D$51=$P2)),(E$2:E$51<>"")*(E$2:E$51=MINIFS(E$2:E$51,$B$2:$B$51,$B$2:$B$51)))

Wow that gets me 90% there thank you! The two missing parts are:
1) Columns N and M are looking for the largest value 100%/1 not the lowest like all the other columns
2) Is there a way to multiple the 1 for each score tallied by the weight of each group in column C?

This is really amazing btw - thank you!

 

[Fluff]

For M & N just change the minifs to maxifs.

 

[Fluff]

And for the weighting try

=MMULT(TRANSPOSE(($B$2:$B$51=$B$2:$B$51)*($D$2:$D$51=$P2)*($C$2:$C$51)),(E$2:E$51<>"")*(E$2:E$51=MINIFS(E$2:E$51,$B$2:$B$51,$B$2:$B$51)))