For a tiebreaker question on a quiz, I need a formula that does the following:
- First, create a ranking based on the column total score
- In case of a tie in previous, the team closest to the answer to the tiebreaker question should come first
So I thought to work with a rank formula first and supplement it with a rank formula where (correct answer - their answer to the tiebreaker question) should be as close to zero as possible.
But I still didn't find the correct formula. Can anyone help me?
rank, countif(s) or sumproduct may help
Attach a sample sheet.
Quang PT
Here the file
Quiz Scoreblad Editie II.xlsx
USe SUMPRODUCT() to count how many rows:
1) Total>current Total
If there are more than dupplicated same total, then next
2) Column (D-D1)/1000> current (D-D1)/1000
For example: row 3 and row 28, with same Total = 72
Row 3: current value = total - ABS(D-D1)/1000 = 72 - ABS(256-300) = 72 -0.044 = 71.956
Row 28: current value = total - ABS(D-D1)/1000 = 72 - ABS(576-300) = 72 -0.276 = 71.724
Now, there is only 1 row >= row 3, then row3 = 1
Now, there are 2 rows >= row 28, then row 28 = 2
That is how SUMPRODUCT works.
In P3:
drag down
Please Login or Register to view this content.
Delete delete
Last edited by josephteh; 03-03-2023 at 07:27 AM.
This gives the reverse answer... Now the Tiebreaker question decides who will win
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks