Quiz League Table

keithobro

New Member
Joined
Aug 1, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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
    Excel help snip.JPG
    139.7 KB · Views: 7

Some videos you may like

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.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
996
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 1, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
996
Office Version
  1. 2016
Platform
  1. Windows
You're welcome!
Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,112
Messages
5,546,012
Members
410,720
Latest member
SSL
Top