Quiz League Table

[keithobro]

I run a weekly online quiz with anything from 35-50 people taking part. I assign everyone into teams each week. They may be 7-10 teams each week. I then run a "league table" of results. If there are 9 teams, each member of the winning team scores 9 pts. 2nd place 8 pts etc.

I would like to know how to input a formula which will review each individual player's scores and return how many times they have featured in the winning team over a period. So far, I've only managed to colour format these events, but even that was laborious.

So, basically, what formula would I need to use in the next column to the right to obtain the number of times a player has featured in the winning team. You will see that at the bottom of each week's column, I already have the highest score for each week.

I have hidden several rows at the bottom of the spreadsheet so that I can take an image.

 

[Toadstool]

Hi KeithObro,

I'm not sure if I've understood the APPS column (Action Player PointS?) but is this what you were looking for?

KeithObro.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	12-Jun-20	19-Jun-20	26-Jun-20	03-Jul-20	10-Jul-20	17-Jul-20	24-Jul-20	31-Jul-20	Total	APPS	Average	Highest	No. of Wins
2	Fred	6	6	3	9	4	8	4	8	48	8	6	9	3
3	Bert	9	3	6	5	7	7	3	7	47	8	5.9	9	1
4	Susan	3	6	9	8	6	5	4	3	44	8	5.5	9	2
5	Tom	6	8	8	8	1	9	1	1	42	8	5.3	9	2
6	Sarah	7	7		5	8	7		6	40	6	6.7	8	1
7	Charles	8	5	5	2	7	5	2	4	38	8	4.8	8	0
8
9	Highest Score	9	8	9	9	8	9	4	8
Sheet1

Cell Formulas
Range	Formula
J2:J7	J2	=SUM(B2:I2)
K2:K7	K2	=ROUND(J2/L2,0)
L2:L7	L2	=ROUND(AVERAGE(B2:I2),1)
M2:M7	M2	=MAX(B2:I2)
N2:N7	N2	=SUMPRODUCT(--(B2:I2=$B$9:$I$9))
B9:I9	B9	=MAX(B2:B8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:I7	Expression	=B2=B$9	text	NO

 

[keithobro]

Wow! Thanks for all of that. The only column I really needed help on was column N in your example and your solution has done exactly what I required.

Thank you kindly.

Keith

 

[Toadstool]

You're welcome!
Thanks for the feedback.