Ranking people from a table I have

[Ramballah]

Hi everyone,

I have a table where I have people with 'scores' next to them. I want to basically make some kind of new table where those people are ranked for the week.
Basically I want the new table to have all the people that participated listed in there automatically and then count how many times their score is 12 or higher. With that I can get percentages of how often they score etc.
Basically:
Person A participated 6 times, and out of those 6 times only 3 times his score was above 12 so he scores a 50%. makes sense right??? I know this doesnt but bear with me.

Ma, Di, Wo, Do, Vrij, Za, Zo are just the days and then u can see basically the names and their scores infront of them. Red being below 12.
So I want a week summary with all the people (I dont have a list of all the names as the names switch every week).
Basically Ivan would come to this: participated 9 times, 3 times he scored below 12. so he gets a 66,67% basically.
Tobey participated 2 times, got both of them above 12 so he gets a 100%.

I hope this now all makes sense and hopefully someone can help me with this!

Aantal tijden				Ma	Wie	Di	Wie	Wo	Wie	Do	Wie	Vrij	Wie	Za	Wie	Zo	Wie
8-9				0	Ivan	20	Tobey	9	Ivan	22	Ivan	13
9-10				0	Ivan	26	Tobey	17	Ivan	20	Ivan	21
10-11				25	Patricia	37	Miranda	19	Dana	25	Miranda	15
11-12				4	Mara	4	Miranda	30	Ivan	0	Patricia	22
12-13				13	Madelief	43	Ivan	14	Sterre	6	Patricia	25
13-14				23	Patricia	34	Mara	23	Alisa	12	Mara	24
14-15				23	Ashley	15	Patricia	16	Madelief	14	Miranda	26
15-16				2	Madelief	20	Miranda	6	Sterre	8	Jill	31
16-17				17	Ashley	17	Ivan	6	Dilan	4	Jill	0	?
17-18				34	Zara	29	Emre	31	Alisa	3	Zara	0	?
18-19				2	Yinthe	29	Dilan	57	Anne	12	Mara	0	?
19-20				9	Zara	8	Dana	6	Judith	7	Anne	0	?
20-21				0	Sterre	1	Emre	0	Anne	6	Anne	0	?
Totaal				152		283		234		139		177		0		0

Total	Succeed	Fail	%
Ivan	9	6	3	66,67%
Tobey	2	2	0	100%

 

[Ramballah]

For the new layout

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Tijden				Naam	Ma	Naam	Di	Naam	Wo	Naam	Do	Naam	Vrij	Naam	Za	Naam	Zo
2	08:00-09:00				Ivan	0	Tobey	20	Ivan	9	Ivan	22		13
3	09:00-10:00				Ivan	0	Tobey	26	Ivan	17	Ivan	20		21
4	10:00-11:00				Patricia	25	Miranda	37	Dana	19	Miranda	25		15
5	11:00-12:00				Mara	4	Miranda	4	Ivan	30	Patricia	0		22
6	12:00-13:00				Madelief	13	Ivan	43	Sterre	14	Patricia	6		25
7	13:00-14:00				Patricia	23	Mara	34	Alisa	23	Mara	12		24
8	14:00-15:00				Ashley	23	Patricia	15	Madelief	16	Miranda	14		26
9	15:00-16:00				Madelief	2	Miranda	20	Sterre	6	Jill	8		31
10	16:00-17:00				Ashley	17	Ivan	17	Dilan	6	Jill	4
11	17:00-18:00				Zara	34	Emre	29	Alisa	31	Zara	3
12	18:00-19:00				Yinthe	2	Dilan	29	Anne	57	Mara	12
13	19:00-20:00				Zara	9	Dana	8	Judith	6	Anne	7
14	20:00-21:00				Sterre	0	Emre	1	Anne	0	Anne	6
15	Totaal					152		283		234		139		177		0		0
16
17
18
19
20	Alisa	2	2	0	100.00%
21	Anne	4	1	3	25.00%
22	Ashley	2	2	0	100.00%
23	Dana	2	1	1	50.00%
24	Dilan	2	1	1	50.00%
25	Emre	2	1	1	50.00%
26	Ivan	9	6	3	66.67%
27	Jill	2	0	2	0.00%
28	Judith	1	0	1	0.00%
29	Madelief	3	2	1	66.67%
30	Mara	4	1	1	25.00%
31	Miranda	5	4	1	80.00%
32	Patricia	5	3	2	60.00%
33	Sterre	3	1	2	33.33%
34	Tobey	2	2	0	100.00%
35	Yinthe	1	0	1	0.00%
36	Zara	3	1	2	33.33%
37
Data

Cell Formulas
Range	Formula
A20:E36	A20	=LET(d,WRAPROWS(TOCOL(E2:R14),2),f,FILTER(d,(TAKE(d,,1)<>"")*(TAKE(d,,1)<>"?")),g,DROP(GROUPBY(TAKE(f,,1),TAKE(f,,-1),HSTACK(COUNT,LAMBDA(v,SUM(--(v>12))),LAMBDA(v,SUM(--(v<12)))),,0),1),HSTACK(g,BYROW(g,LAMBDA(br,INDEX(br,,3)/INDEX(br,,2)))))
Dynamic array formulas.

works! thank you so much! Can I sort this on the highest percentages are on top?

 

[Fluff]

How about

=LET(d,WRAPROWS(TOCOL(E2:R14),2),f,FILTER(d,(TAKE(d,,1)<>"")*(TAKE(d,,1)<>"?")),g,DROP(GROUPBY(TAKE(f,,1),TAKE(f,,-1),HSTACK(COUNT,LAMBDA(v,SUM(--(v>12))),LAMBDA(v,SUM(--(v<12)))),,0),1),SORT(HSTACK(g,BYROW(g,LAMBDA(br,INDEX(br,,3)/INDEX(br,,2)))),5,-1))

 

[Ramballah]

How about

=LET(d,WRAPROWS(TOCOL(E2:R14),2),f,FILTER(d,(TAKE(d,,1)<>"")*(TAKE(d,,1)<>"?")),g,DROP(GROUPBY(TAKE(f,,1),TAKE(f,,-1),HSTACK(COUNT,LAMBDA(v,SUM(--(v>12))),LAMBDA(v,SUM(--(v<12)))),,0),1),SORT(HSTACK(g,BYROW(g,LAMBDA(br,INDEX(br,,3)/INDEX(br,,2)))),5,-1))

hero, thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Ramballah]

Glad we could help & thanks for the feedback.

unsure if I need to open a new ticket, but I finished the workbook and it looks amazing. And then my when my 'boss' opened it the formula didnt work. after some looking around I noticed he uses excel 2008... He insists that he doesn't need to update it to 365.. Is there a way this formula can be changed to 2008 functions?

 

[Fluff]

There is no 2008.

 

[Ramballah]

There is no 2008.

Well when he opens the workbook the formula changes to this:

=_xlfn.LET(_xlpm.d;_xlfn.WRAPROWS(_xlfn.TOCOL(C14:P26);2);_xlpm.f;FILTER(_xlpm.d;(_xlfn.TAKE(_xlpm.d;;1)<>"")*(_xlfn.TAKE(_xlpm.d;;1)<>"?"));_xlpm.g;_xlfn.DROP(_xlfn.GROUPBY(_xlfn.TAKE(_xlpm.f;;1);_xlfn.TAKE(_xlpm.f;;-1);_xlfn.HSTACK(_xleta.COUNT;_xlfn.LAMBDA(_xlpm.v;SUM(--(_xlpm.v>12)));_xlfn.LAMBDA(_xlpm.v;SUM(--(_xlpm.v<12))));;0);1);SORT(_xlfn.HSTACK(_xlpm.g;_xlfn.BYROW(_xlpm.g;_xlfn.LAMBDA(_xlpm.br;INDEX(_xlpm.br;;3)/INDEX(_xlpm.br;;2))));5;-1))

So its some version that doesnt have functions like let

 

[Eric W]

Here's how to do it with functions available in Excel 2016:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Tijden				Naam	Ma	Naam	Di	Naam	Wo	Naam	Do	Naam	Vrij	Naam	Za	Naam	Zo
2	08:00-09:00				Ivan	0	Tobey	20	Ivan	9	Ivan	22		13
3	09:00-10:00				Ivan	0	Tobey	26	Ivan	17	Ivan	20		21
4	10:00-11:00				Patricia	25	Miranda	37	Dana	19	Miranda	25		15
5	11:00-12:00				Mara	4	Miranda	4	Ivan	30	Patricia	0		22
6	12:00-13:00				Madelief	13	Ivan	43	Sterre	14	Patricia	6		25
7	13:00-14:00				Patricia	23	Mara	34	Alisa	23	Mara	12		24
8	14:00-15:00				Ashley	23	Patricia	15	Madelief	16	Miranda	14		26
9	15:00-16:00				Madelief	2	Miranda	20	Sterre	6	Jill	8		31
10	16:00-17:00				Ashley	17	Ivan	17	Dilan	6	Jill	4
11	17:00-18:00				Zara	34	Emre	29	Alisa	31	Zara	3
12	18:00-19:00				Yinthe	2	Dilan	29	Anne	57	Mara	12
13	19:00-20:00				Zara	9	Dana	8	Judith	6	Anne	7
14	20:00-21:00				Sterre	0	Emre	1	Anne	0	Anne	6
15	Totaal					152		283		234		139		177		0		0
16
17
18	Name	Successful	Unsuccessful	Total	Percent		Name	Successful	Unsuccessful	Total	Percent	Row
19	Ivan	6	3	9	67%		Tobey	2	0	2	100%	20
20	Tobey	2	0	2	100%		Alisa	2	0	2	100%	27
21	Patricia	3	2	5	60%		Ashley	2	0	2	100%	28
22	Miranda	4	1	5	80%		Miranda	4	1	5	80%	22
23	Dana	1	1	2	50%		Ivan	6	3	9	67%	19
24	Mara	1	3	4	25%		Madelief	2	1	3	67%	25
25	Madelief	2	1	3	67%		Patricia	3	2	5	60%	21
26	Sterre	1	2	3	33%		Dana	1	1	2	50%	23
27	Alisa	2	0	2	100%		Dilan	1	1	2	50%	30
28	Ashley	2	0	2	100%		Emre	1	1	2	50%	32
29	Jill	0	2	2	0%		Sterre	1	2	3	33%	26
30	Dilan	1	1	2	50%		Zara	1	2	3	33%	31
31	Zara	1	2	3	33%		Mara	1	3	4	25%	24
32	Emre	1	1	2	50%		Anne	1	3	4	25%	34
33	Yinthe	0	1	1	0%		Jill	0	2	2	0%	29
34	Anne	1	3	4	25%		Yinthe	0	1	1	0%	33
35	Judith	0	1	1	0%		Judith	0	1	1	0%	35
36
37
38
39
40
Sheet2

Cell Formulas
Range	Formula
A19:A40	A19	=IFERROR(INDEX($1:$14,INT(AGGREGATE(15,6,(ROW($E$2:$R$14)*100+COLUMN($E$2:$R$14))/ISERROR($E$2:$R$14+1)/($E$2:$R$14<>"")/(COUNTIF($A$18:$A18,$E$2:$R$14)=0),1)/100),MOD(AGGREGATE(15,6,(ROW($E$2:$R$14)*100+COLUMN($E$2:$R$14))/ISERROR($E$2:$R$14+1)/($E$2:$R$14<>"")/(COUNTIF($A$18:$A18,$E$2:$R$14)=0),1),100)),"")
B19:B40	B19	=IF($A19="","",COUNTIFS($E$2:$Q$14,A19,$F$2:$R$14,">12"))
C19:C40	C19	=IF($A19="","",COUNTIFS($E$2:$Q$14,A19,$F$2:$R$14,"<=12"))
D19:D40	D19	=IF($A19="","",B19+C19)
E19:E40	E19	=IF($A19="","",B19/D19)
G19:J40	G19	=IF($K19="","",INDEX(A:A,$L19))
K19:K40	K19	=IFERROR(LARGE(E$19:E$40,ROWS(K$19:K19)),"")
L19:L40	L19	=IF(K19="","",AGGREGATE(15,6,ROW(E$19:E$40)/(E$19:E$40=K19),COUNTIFS(K$19:K19,K19)))

The A:E formulas are required. If you want to sort by the percentage, then you can use the G:L formulas, which reference the A:E formulas.

If this still does not work on your boss' computer, then it's still possible using CSE formulas and increasingly complicated formulas, but it'd be tough to get someone to write them. I don't have the capability to test them, since CSE works different on the newer versions.

 

[Ramballah]

Here's how to do it with functions available in Excel 2016:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Tijden				Naam	Ma	Naam	Di	Naam	Wo	Naam	Do	Naam	Vrij	Naam	Za	Naam	Zo
2	08:00-09:00				Ivan	0	Tobey	20	Ivan	9	Ivan	22		13
3	09:00-10:00				Ivan	0	Tobey	26	Ivan	17	Ivan	20		21
4	10:00-11:00				Patricia	25	Miranda	37	Dana	19	Miranda	25		15
5	11:00-12:00				Mara	4	Miranda	4	Ivan	30	Patricia	0		22
6	12:00-13:00				Madelief	13	Ivan	43	Sterre	14	Patricia	6		25
7	13:00-14:00				Patricia	23	Mara	34	Alisa	23	Mara	12		24
8	14:00-15:00				Ashley	23	Patricia	15	Madelief	16	Miranda	14		26
9	15:00-16:00				Madelief	2	Miranda	20	Sterre	6	Jill	8		31
10	16:00-17:00				Ashley	17	Ivan	17	Dilan	6	Jill	4
11	17:00-18:00				Zara	34	Emre	29	Alisa	31	Zara	3
12	18:00-19:00				Yinthe	2	Dilan	29	Anne	57	Mara	12
13	19:00-20:00				Zara	9	Dana	8	Judith	6	Anne	7
14	20:00-21:00				Sterre	0	Emre	1	Anne	0	Anne	6
15	Totaal					152		283		234		139		177		0		0
16
17
18	Name	Successful	Unsuccessful	Total	Percent		Name	Successful	Unsuccessful	Total	Percent	Row
19	Ivan	6	3	9	67%		Tobey	2	0	2	100%	20
20	Tobey	2	0	2	100%		Alisa	2	0	2	100%	27
21	Patricia	3	2	5	60%		Ashley	2	0	2	100%	28
22	Miranda	4	1	5	80%		Miranda	4	1	5	80%	22
23	Dana	1	1	2	50%		Ivan	6	3	9	67%	19
24	Mara	1	3	4	25%		Madelief	2	1	3	67%	25
25	Madelief	2	1	3	67%		Patricia	3	2	5	60%	21
26	Sterre	1	2	3	33%		Dana	1	1	2	50%	23
27	Alisa	2	0	2	100%		Dilan	1	1	2	50%	30
28	Ashley	2	0	2	100%		Emre	1	1	2	50%	32
29	Jill	0	2	2	0%		Sterre	1	2	3	33%	26
30	Dilan	1	1	2	50%		Zara	1	2	3	33%	31
31	Zara	1	2	3	33%		Mara	1	3	4	25%	24
32	Emre	1	1	2	50%		Anne	1	3	4	25%	34
33	Yinthe	0	1	1	0%		Jill	0	2	2	0%	29
34	Anne	1	3	4	25%		Yinthe	0	1	1	0%	33
35	Judith	0	1	1	0%		Judith	0	1	1	0%	35
36
37
38
39
40
Sheet2

Cell Formulas
Range	Formula
A19:A40	A19	=IFERROR(INDEX($1:$14,INT(AGGREGATE(15,6,(ROW($E$2:$R$14)*100+COLUMN($E$2:$R$14))/ISERROR($E$2:$R$14+1)/($E$2:$R$14<>"")/(COUNTIF($A$18:$A18,$E$2:$R$14)=0),1)/100),MOD(AGGREGATE(15,6,(ROW($E$2:$R$14)*100+COLUMN($E$2:$R$14))/ISERROR($E$2:$R$14+1)/($E$2:$R$14<>"")/(COUNTIF($A$18:$A18,$E$2:$R$14)=0),1),100)),"")
B19:B40	B19	=IF($A19="","",COUNTIFS($E$2:$Q$14,A19,$F$2:$R$14,">12"))
C19:C40	C19	=IF($A19="","",COUNTIFS($E$2:$Q$14,A19,$F$2:$R$14,"<=12"))
D19:D40	D19	=IF($A19="","",B19+C19)
E19:E40	E19	=IF($A19="","",B19/D19)
G19:J40	G19	=IF($K19="","",INDEX(A:A,$L19))
K19:K40	K19	=IFERROR(LARGE(E$19:E$40,ROWS(K$19:K19)),"")
L19:L40	L19	=IF(K19="","",AGGREGATE(15,6,ROW(E$19:E$40)/(E$19:E$40=K19),COUNTIFS(K$19:K19,K19)))

The A:E formulas are required. If you want to sort by the percentage, then you can use the G:L formulas, which reference the A:E formulas.

If this still does not work on your boss' computer, then it's still possible using CSE formulas and increasingly complicated formulas, but it'd be tough to get someone to write them. I don't have the capability to test them, since CSE works different on the newer versions.

after trying ur formula and changing the references to my ranges i then only get "Sterre"

=IFERROR(INDEX($13:$26;INT(AGGREGATE(15;6;(ROW($C$14:$P$26)*100+COLUMN($C$14:$P$26))/ISERROR($C$14:$P$26+1)/($C$14:$P$26<>"")/(COUNTIF($X$1:$X1;$C$14:$P$26)=0);1)/100);MOD(AGGREGATE(15;6;(ROW($C$14:$P$26)*100+COLUMN($C$14:$P$26))/ISERROR($C$14:$P$26+1)/($C$14:$P$26<>"")/(COUNTIF($X$1:$X1;$C$14:$P$26)=0);1);100));"")

 

[Eric W]

Presumably your formula is in X2? These older formulas are much more sensitive to range positioning than the new functions. I believe that all you need to do to fix the formula is change the start of it to:

=IFERROR(INDEX($1:$26;

The way I designed it, the INDEX range should always start at 1, and end at the lower bound of your table. There are ways to adapt the formula like you did, but at the expense of making the formula longer and more complicated. If this does not fix the problem, let me know exactly where your table is, and where you want the results, and I'll adapt it accordingly.