Quiz Scores Leaderboard

[stefanaalten]

Hi,

I'm trying to compile a "leaderboard" for our weekly quizzes, which since the start of the pandemic have become our mainstay social activity (and good fun they are too). It's only among friends but can get pretty competitive! I have produced a spreadsheet to record the weekly scores, but want to produce an overall ranking, over time, showing which team has come in which place in a given week (based on the total score for that week).

So the "output" I'm looking to produce shows for a given quiz date basis which team has come 1st, 2nd, 3rd, 4th (i.e. the actual scores are immaterial - it's only the ranking that matters).

Can anyone please help me on my way? I've tried playing around with pivot tables but am getting mightily confused! Probably a really simple "problem" to solve but I'm not really sure how to go about, and any help would be appreciated!

My source data is like this:

Scores on the Doors - Totals.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Row ID	Quiz Date	Host	Round No.	Round Title	A&R	E&M	M&M	N&S	W&M
2	1	2021-01-08	W&M	1	Colours	3	5	4	3
3	2	2021-01-08	W&M	2	Water	6	3	10	8
4	3	2021-01-08	W&M	3	Sheep or Rabbit	6	5	7	5
5	4	2021-01-08	W&M	4	70's	8	9.5	10.5	9.5
6	5	2021-01-08	W&M	5	Drink	3	2	2	6
7	6	2021-01-22	E&M	1	80's pop	5		11	6	9
8	7	2021-01-22	E&M	2	Films/songs	10		12	4	16
9	8	2021-01-22	E&M	3	G7	13		14	13	15
10	9	2021-01-22	E&M	4	Film soundtracks	4		4	7	8
11	10	2021-01-22	E&M	5	Live Aid	8		10	7	13
12	11	2021-01-29	A&R	1	Fruit & Veg		12	15	15	15
13	12	2021-01-29	A&R	2	Trump		6	3	5	4
14	13	2021-01-29	A&R	3	Dance Moves		7	4	8	7
15	14	2021-01-29	A&R	4	In Continent		6	5	9	11
16	15	2021-01-29	A&R	5	Spot the Dog		25	25	19	26
17	16	2021-02-05	M&M	1	Knobs & Knockers	2.5	5		3.5	5
18	17	2021-02-05	M&M	2	DIY	2.5	4		3.5	5
19	18	2021-02-05	M&M	3	Arts & Crafts	2	5		7	4.5
20	19	2021-02-05	M&M	4	Sport	4	4		2	5
21	20	2021-02-05	M&M	5	Picture Round	14	18.5		17	21.5
22	21	2021-02-05	M&M	6	Silly	6	5.5		3	6
23	22	2021-02-12	W&M	1	1980's	4	5	5	5
24	23	2021-02-12	W&M	2	British Wildlife	6	3	5	6
25	24	2021-02-12	W&M	3	American or Canadian	7	8	7	7
26	25	2021-02-12	W&M	4	Just Desserts	7.5	5.5	8	8
27	26	2021-02-12	W&M	5	Cats	19	21	27	15.5
28	27	2021-02-19	N&S	1	Where Am I?	6	7	7		5
29	28	2021-02-19	N&S	2	Alphabetical Capitals	9	12	15		12
30	29	2021-02-19	N&S	3	Hungarian Phrasebook	5	5	8		7
31	30	2021-02-19	N&S	4	Parlez-vous Inuit?	7	10	10		10
32	31	2021-02-19	N&S	5	World Music	6	7	6		9
33	32	2021-02-19	N&S	6	Bonus Round: Fruit & Veg	14.5	11	14		13
34	33	2021-02-26	E&M	1	By the Bard	7		7	5	6
35	34	2021-02-26	E&M	2	Tallest and Heaviest	6		7	3	3
36	35	2021-02-26	E&M	3	Good Citizen	6		5	5	8
37	36	2021-02-26	E&M	4	Sex Pests	10		13	14	13
38	37	2021-02-26	E&M	5	Colours	4		3	2	4
Scores

 

[stefanaalten]

One final, final ? question ... What if you wanted a week by week cumulative "overall" score (instead of only the "current overall" as shown in N2:R2)? Maybe in some extra columns to the right of column R, showing the calculation being performed in R2:N2?

 

[Fluff]

How about

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Quiz/Team	A&R	E&M	M&M	N&S	W&M		A&R	E&M	M&M	N&S	W&M		A&R	E&M	M&M	N&S	W&M
2	Overall	26	46	70	30	67
3	08/01/2021	3	4	1	2	Hosts		20	10	100	50	0		26	24.5	33.5	31.5	0
4	15/01/2021	3	4	1	Hosts	2		20	10	100	50	50		38.5	34	47	0	45
5	22/01/2021	3	Hosts	2	4	1		20	10	83	30	75		40	0	51	37	61
6	29/01/2021	Hosts	2	4	2	1		20	23	65	37	83		0	56	52	56	63
7	05/02/2021	4	2	Hosts	3	1		18	30	65	33	88		31	42	0	36	47
8	12/02/2021	2	3	1	4	Hosts		24	28	72	28	88		43.5	42.5	52	41.5	0
9	19/02/2021	4	3	1	Hosts	2		22	27	77	28	80		47.5	52	60	0	56
10	26/02/2021	3	Hosts	1	4	2		21	27	80	25	75		33	0	35	29	34
11	05/03/2021	Hosts	4	1	3	2		21	24	83	24	71		0	50	56	51	52
12	12/03/2021	4	1	Hosts	2	2		20	34	83	28	69		54	65	0	55	55
13	19/03/2021	3	2	1	4	Hosts		20	36	84	26	69		35	45	55	29	0
14	26/03/2021	2	1	4	Hosts	3		23	42	77	26	63		36	38.5	30.5	0	35
15	09/04/2021	1	Hosts	-	3	2		30	42	77	25	62		62.5	0	0	54	61.5
16	23/04/2021	Hosts	1	-	3	2		30	47	77	25	61		0	53.75	0	50	53.5
17	30/04/2021	4	3	2	1	Hosts		28	45	75	31	61		41.5	52.5	54	59.5	0
18	07/05/2021	4	2	Hosts	3	1		27	45	75	30	64		27	33	0	31	42
19	14/05/2021	3	2	3	Hosts	1		26	46	70	30	67		46	50	46	0	65
20
Master

Cell Formulas
Range	Formula
N2:R2	N2	=SUM(COUNTIFS(N3:N100,{1,2,3,4})*{100,50,20,10})/COUNTIF(N3:N100,"<5")
N3:R19	N3	=IF($M3="","",IF(COUNTIFS($B:$B,$M3,$C:$C,N$1),"Hosts",IF(Z3=0,"-",RANK(Z3,$Z3:$AD3))))
T3:X19	T3	=IFERROR(SUM(COUNTIFS(N$3:N3,{1,2,3,4})*{100,50,20,10})/COUNTIF(N$3:N3,"<5"),0)
Z3:AD19	Z3	=SUMIF($B:$B,$M3,F:F)

 

[stefanaalten]

Many thanks Fluff! There's no way I would have been able to work that out for myself and I really appreciate your help. Folks like you, who so selflessly give their expertise for the benefit of others, make MrExcel such a great forum.

 

[Fluff]

My pleasure & thanks for the kind words.