# Quiz League Table

#### keithobro

##### New Member
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.

#### Attachments

• Excel help snip.JPG
139.7 KB · Views: 7

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

##### Well-known Member
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
ABCDEFGHIJKLMN
1Name12-Jun-2019-Jun-2026-Jun-2003-Jul-2010-Jul-2017-Jul-2024-Jul-2031-Jul-20TotalAPPSAverageHighestNo. of Wins
2Fred66394848488693
3Bert936577374785.991
4Susan369865434485.592
5Tom688819114285.392
6Sarah7758764066.781
7Charles855275243884.880
8
9Highest Score98998948
Sheet1
Cell Formulas
RangeFormula
J2:J7J2=SUM(B2:I2)
K2:K7K2=ROUND(J2/L2,0)
L2:L7L2=ROUND(AVERAGE(B2:I2),1)
M2:M7M2=MAX(B2:I2)
N2:N7N2=SUMPRODUCT(--(B2:I2=\$B\$9:\$I\$9))
B9:I9B9=MAX(B2:B8)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:I7Expression=B2=B\$9textNO

#### keithobro

##### New Member
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

##### Well-known Member
You're welcome!
Thanks for the feedback.

Replies
3
Views
225
Replies
0
Views
92
Replies
23
Views
397
Replies
1
Views
22
Replies
7
Views
93