Ranking people from a table I have

Ramballah · Saturday at 10:23 AM

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%

alansidman · Saturday at 11:42 AM

With Power query

Book2

A

B

C

D

18

Value

Count

Number >12

Division

19

Ivan

9

7

0.777777778

20

Tobey

2

1

0.5

21

Patricia

5

1

22

Miranda

5

4

0.8

23

Dana

2

1

0.5

24

Mara

4

2

0.5

25

Madelief

3

1

26

Sterre

3

0

27

Alisa

2

0

28

Ashley

2

1

29

Jill

2

1

0.5

30

Dilan

2

1

0.5

31

Zara

3

1

0.333333333

32

Emre

2

1

0.5

33

Yinthe

1

34

Anne

4

0

35

Judith

1

0

Sheet1

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Aantal tijden] <> "Totaal")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Aantal tijden", "Wie", "Di", "Wie4", "Wo", "Wie5", "Do", "Wie6", "Vrij"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Aantal tijden"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each try Type.Is(Text.InferNumberType(Text.From([Value])), type number) otherwise false),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = true then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([Custom] = false)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom.2", each if [Custom.1] > 12 then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Number >12", each List.Sum([Custom.2]), type number}}),
    #"Inserted Division" = Table.AddColumn(#"Grouped Rows", "Division", each [#"Number >12"] / [Count], type number)
in
    #"Inserted Division"

Eric W · Saturday at 12:04 PM

Try:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Aantal tijden

Ma

Wie

Di

Wie

Wo

Wie

Do

Wie

Vrij

Wie

Za

Wie

Zo

Wie

2

9-Aug

0

Ivan

20

Tobey

9

Ivan

22

Ivan

13

3

10-Sep

0

Ivan

26

Tobey

17

Ivan

20

Ivan

21

4

11-Oct

25

Patricia

37

Miranda

19

Dana

25

Miranda

15

5

12-Nov

4

Mara

4

Miranda

30

Ivan

0

Patricia

22

6

13-Dec

13

Madelief

43

Ivan

14

Sterre

6

Patricia

25

7

13-14

23

Patricia

34

Mara

23

Alisa

12

Mara

24

8

14-15

23

Ashley

15

Patricia

16

Madelief

14

Miranda

26

9

15-16

2

Madelief

20

Miranda

6

Sterre

8

Jill

31

10

16-17

17

Ashley

17

Ivan

6

Dilan

4

Jill

0

?

11

17-18

34

Zara

29

Emre

31

Alisa

3

Zara

0

?

12

18-19

2

Yinthe

29

Dilan

57

Anne

12

Mara

0

?

13

19-20

9

Zara

8

Dana

6

Judith

7

Anne

0

?

14

20-21

0

Sterre

1

Emre

0

Anne

6

Anne

0

?

15

Totaal

152

283

234

139

177

0

16

17

Name

Successful

Not Successful

Percent

18

Alisa

2

0

100%

19

Ashley

2

0

100%

20

Dana

1

50%

21

Ivan

6

3

67%

22

Madelief

2

1

67%

23

Mara

1

3

25%

24

Miranda

4

1

80%

25

Patricia

3

2

60%

26

Sterre

1

2

33%

27

Tobey

2

0

100%

Sheet1

Cell Formulas
Range	Formula
A18:D27	A18	=LET(arr,E2:R14,s,SEQUENCE(ROWS(arr)7,,0),I,INDEX(arr,MOD(s,7)+1,(INT(s/7)+1)2),n,IF(ISTEXT(I),I,""),u,SORT(UNIQUE(FILTER(n,(n<>"?")*(n<>"")))),a,COUNTIFS(arr,">12",OFFSET(arr,0,1),u),b,COUNTIFS(arr,"<=12",OFFSET(arr,0,1),u),CHOOSE({1,2,3,4},u,a,b,a/(a+b)))
Dynamic array formulas.

I did this on Excel 2021. It would be easier with the TOCOL and HSTACK functions which 365 has, but 2021 lacks.

Ramballah · Saturday at 12:28 PM

alansidman said:

With Power query

Book2

A

B

C

D

18

Value

Count

Number >12

Division

19

Ivan

9

7

0.777777778

20

Tobey

2

1

0.5

21

Patricia

5

1

22

Miranda

5

4

0.8

23

Dana

2

1

0.5

24

Mara

4

2

0.5

25

Madelief

3

1

26

Sterre

3

0

27

Alisa

2

0

28

Ashley

2

1

29

Jill

2

1

0.5

30

Dilan

2

1

0.5

31

Zara

3

1

0.333333333

32

Emre

2

1

0.5

33

Yinthe

1

34

Anne

4

0

35

Judith

1

0

Sheet1

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Aantal tijden] <> "Totaal")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Aantal tijden", "Wie", "Di", "Wie4", "Wo", "Wie5", "Do", "Wie6", "Vrij"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Aantal tijden"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each try Type.Is(Text.InferNumberType(Text.From([Value])), type number) otherwise false),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = true then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([Custom] = false)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom.2", each if [Custom.1] > 12 then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Number >12", each List.Sum([Custom.2]), type number}}),
    #"Inserted Division" = Table.AddColumn(#"Grouped Rows", "Division", each [#"Number >12"] / [Count], type number)
in
    #"Inserted Division"

Hi,
Ur code works almost perfectly, I tried adjusting it to make it work but I failed somewhere, also upon using ur code I realized I have numbers first and then a name behind it which I now changed for better reading sakes. Anyway I also failed to say that I use just a data range and not a table format. Is it possible to have this work on just the names and numbers without changing all my merged cells/headers/formats?

Besides that I changed it to this for now:

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Tijden] <> "Totaal")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Tijden", "Naam", "Ma", "Naam4", "Di", "Naam5", "Wo", "Naam6", "Vrij", "Naam7", "Za", "Naam8", "Zo", "Naam9"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Tijden"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each try Type.Is(Text.InferNumberType(Text.From([Value])), type number) otherwise false),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = true then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([Custom] = false)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom.2", each if [Custom.1] > 12 then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Number >12", each List.Sum([Custom.2]), type number}}),
    #"Inserted Division" = Table.AddColumn(#"Grouped Rows", "Division", each [#"Number >12"] / [Count], type number)
in
    #"Inserted Division"

which gets me this:

As shown, some errors
For anne it should be 4 recordings, 3 failed 1 good.



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

Ramballah · Saturday at 12:33 PM

Eric W said:
Try:

Book1
A B C D E F G H I J K L M N O P Q R
1 Aantal tijden Ma Wie Di Wie Wo Wie Do Wie Vrij Wie Za Wie Zo Wie
2 9-Aug 0 Ivan 20 Tobey 9 Ivan 22 Ivan 13
3 10-Sep 0 Ivan 26 Tobey 17 Ivan 20 Ivan 21
4 11-Oct 25 Patricia 37 Miranda 19 Dana 25 Miranda 15
5 12-Nov 4 Mara 4 Miranda 30 Ivan 0 Patricia 22
6 13-Dec 13 Madelief 43 Ivan 14 Sterre 6 Patricia 25
7 13-14 23 Patricia 34 Mara 23 Alisa 12 Mara 24
8 14-15 23 Ashley 15 Patricia 16 Madelief 14 Miranda 26
9 15-16 2 Madelief 20 Miranda 6 Sterre 8 Jill 31
10 16-17 17 Ashley 17 Ivan 6 Dilan 4 Jill 0 ?
11 17-18 34 Zara 29 Emre 31 Alisa 3 Zara 0 ?
12 18-19 2 Yinthe 29 Dilan 57 Anne 12 Mara 0 ?
13 19-20 9 Zara 8 Dana 6 Judith 7 Anne 0 ?
14 20-21 0 Sterre 1 Emre 0 Anne 6 Anne 0 ?
15 Totaal 152 283 234 139 177 0 0
16
17 Name Successful Not Successful Percent
18 Alisa 2 0 100%
19 Ashley 2 0 100%
20 Dana 1 1 50%
21 Ivan 6 3 67%
22 Madelief 2 1 67%
23 Mara 1 3 25%
24 Miranda 4 1 80%
25 Patricia 3 2 60%
26 Sterre 1 2 33%
27 Tobey 2 0 100%
Sheet1
Cell Formulas
Range Formula
A18:D27 A18 =LET(arr,E2:R14,s,SEQUENCE(ROWS(arr)*7,,0),I,INDEX(arr,MOD(s,7)+1,(INT(s/7)+1)*2),n,IF(ISTEXT(I),I,""),u,SORT(UNIQUE(FILTER(n,(n<>"?")*(n<>"")))),a,COUNTIFS(arr,">12",OFFSET(arr,0,1),u),b,COUNTIFS(arr,"<=12",OFFSET(arr,0,1),u),CHOOSE({1,2,3,4},u,a,b,a/(a+b)))
Dynamic array formulas.

I did this on Excel 2021. It would be easier with the TOCOL and HSTACK functions which 365 has, but 2021 lacks.

My range where just the names and numbers are is E18:R30. I tried changing it but as i just mentioned in my previous reply to someone else I switched the names and numbers. so now i get

is it possible to change the formula to my current range setup? and also add a column with total amount of attempts?



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

To which E18 is "Ivan" and R30 is empty on sunday right now.

Fluff · Saturday at 12:36 PM

Another option

Fluff.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Aantal tijden

Ma

Wie

Di

Wie

Wo

Wie

Do

Wie

Vrij

Wie

Za

Wie

Zo

Wie

2

08-09

0

Ivan

20

Tobey

9

Ivan

22

Ivan

13

3

09-10

0

Ivan

26

Tobey

17

Ivan

20

Ivan

21

4

10-11

25

Patricia

37

Miranda

19

Dana

25

Miranda

15

5

11-12

4

Mara

4

Miranda

30

Ivan

0

Patricia

22

6

12-13

13

Madelief

43

Ivan

14

Sterre

6

Patricia

25

7

13-14

23

Patricia

34

Mara

23

Alisa

12

Mara

24

8

14-15

23

Ashley

15

Patricia

16

Madelief

14

Miranda

26

9

15-16

2

Madelief

20

Miranda

6

Sterre

8

Jill

31

10

16-17

17

Ashley

17

Ivan

6

Dilan

4

Jill

0

?

11

17-18

34

Zara

29

Emre

31

Alisa

3

Zara

0

?

12

18-19

2

Yinthe

29

Dilan

57

Anne

12

Mara

0

?

13

19-20

9

Zara

8

Dana

6

Judith

7

Anne

0

?

14

20-21

0

Sterre

1

Emre

0

Anne

6

Anne

0

?

15

Totaal

152

283

234

139

177

0

16

17

18

19

20

Alisa

2

0

100.00%

21

Anne

4

1

3

25.00%

22

Ashley

2

0

100.00%

23

Dana

2

1

50.00%

24

Dilan

2

1

50.00%

25

Emre

2

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

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

0

100.00%

35

Yinthe

1

0

1

0.00%

36

Zara

3

1

2

33.33%

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.

Eric W · Saturday at 12:42 PM

Try:

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

16

17

Name

Success

Not Success

Total

Percent

18

Alisa

2

0

2

100%

19

Ashley

2

0

2

100%

20

Dana

1

2

50%

21

Ivan

6

3

9

67%

22

Madelief

2

1

3

67%

23

Mara

1

3

4

25%

24

Miranda

4

1

5

80%

25

Patricia

3

2

5

60%

26

Sterre

1

2

3

33%

27

Tobey

2

0

2

100%

Sheet2

Cell Formulas
Range	Formula
A18:E27	A18	=LET(arr,E2:R14,s,SEQUENCE(ROWS(arr)7,,0),I,INDEX(arr,MOD(s,7)+1,INT(s/7)2+1),n,IF(ISTEXT(I),I,""),u,SORT(UNIQUE(FILTER(n,(n<>"?")*(n<>"")))),a,COUNTIFS(OFFSET(arr,0,1),">12",arr,u),b,COUNTIFS(OFFSET(arr,0,1),"<=12",arr,u),CHOOSE({1,2,3,4,5},u,a,b,a+b,a/(a+b)))
Dynamic array formulas.

Ramballah · Saturday at 12:51 PM

Eric W said:
Try:

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 Name Success Not Success Total Percent
18 Alisa 2 0 2 100%
19 Ashley 2 0 2 100%
20 Dana 1 1 2 50%
21 Ivan 6 3 9 67%
22 Madelief 2 1 3 67%
23 Mara 1 3 4 25%
24 Miranda 4 1 5 80%
25 Patricia 3 2 5 60%
26 Sterre 1 2 3 33%
27 Tobey 2 0 2 100%
Sheet2
Cell Formulas
Range Formula
A18:E27 A18 =LET(arr,E2:R14,s,SEQUENCE(ROWS(arr)*7,,0),I,INDEX(arr,MOD(s,7)+1,INT(s/7)*2+1),n,IF(ISTEXT(I),I,""),u,SORT(UNIQUE(FILTER(n,(n<>"?")*(n<>"")))),a,COUNTIFS(OFFSET(arr,0,1),">12",arr,u),b,COUNTIFS(OFFSET(arr,0,1),"<=12",arr,u),CHOOSE({1,2,3,4,5},u,a,b,a+b,a/(a+b)))
Dynamic array formulas.

I get this

Fluff · Saturday at 12:55 PM

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

16

17

18

19

20

Alisa

2

0

100.00%

21

Anne

4

1

3

25.00%

22

Ashley

2

0

100.00%

23

Dana

2

1

50.00%

24

Dilan

2

1

50.00%

25

Emre

2

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

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

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.

Ramballah · Saturday at 12:56 PM

Fluff said:
Another option

Fluff.xlsm
A B C D E F G H I J K L M N O P Q R
1 Aantal tijden Ma Wie Di Wie Wo Wie Do Wie Vrij Wie Za Wie Zo Wie
2 08-09 0 Ivan 20 Tobey 9 Ivan 22 Ivan 13
3 09-10 0 Ivan 26 Tobey 17 Ivan 20 Ivan 21
4 10-11 25 Patricia 37 Miranda 19 Dana 25 Miranda 15
5 11-12 4 Mara 4 Miranda 30 Ivan 0 Patricia 22
6 12-13 13 Madelief 43 Ivan 14 Sterre 6 Patricia 25
7 13-14 23 Patricia 34 Mara 23 Alisa 12 Mara 24
8 14-15 23 Ashley 15 Patricia 16 Madelief 14 Miranda 26
9 15-16 2 Madelief 20 Miranda 6 Sterre 8 Jill 31
10 16-17 17 Ashley 17 Ivan 6 Dilan 4 Jill 0 ?
11 17-18 34 Zara 29 Emre 31 Alisa 3 Zara 0 ?
12 18-19 2 Yinthe 29 Dilan 57 Anne 12 Mara 0 ?
13 19-20 9 Zara 8 Dana 6 Judith 7 Anne 0 ?
14 20-21 0 Sterre 1 Emre 0 Anne 6 Anne 0 ?
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%
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.

I am sorry i just changed my format during the process of all this so im afraid I dont know how to make this work with the new format



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

Ranking people from a table I have

Ramballah

Active Member

alansidman

Well-known Member

Eric W

MrExcel MVP

Ramballah

Active Member

Ramballah

Active Member

Fluff

MrExcel MVP, Moderator

Eric W

MrExcel MVP

Ramballah

Active Member

Fluff

MrExcel MVP, Moderator

Ramballah

Active Member

Similar threads

Share this page

Ranking people from a table I have

Active Member

Well-known Member

MrExcel MVP

Active Member

Active Member

MrExcel MVP, Moderator

MrExcel MVP

Active Member

MrExcel MVP, Moderator

Active Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock