Scoring system for competition

A

adurham22926192

New Member
Joined
Dec 17, 2019
Messages
49
Office Version
  1. 2019
Platform
  1. Windows
ABCDEFGH
1NumberTotal MarksPoints AwardedRank9NumberTotal MarksPoints Awarded
2163991001
319398492
415697243
598948.54
6179948.54
7629356
8969247
99590.538
1015589.529
1110180110
1210570011

In Column A is the competitor number.
in Column B is the total marks they received
in column C is the points they were awarded
cooumn d is the rank

basically, i want to show the top nth number of results in columns f to h Based on the number that is in cell E1. In E1, it says 9 so in columns f to h I want the data from a2:c10 to show up as they are the top 9 competitors. But if the number in E1 was 8, I only want the top 8 competitors to show up in F:H. I hope this makes sence! Thanks
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
i'm sure there maybe a better way - other member may show

I'm assuming you are using 2019 version , as shown in your profile , so you do NOT have the filter function , which would make this much easier, and just use one function FILTER() to do it all, maybe with a sort

anyway

in F2and copy down

=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))

In G2 and copy down

=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")

In H2 and copy down

=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")

Book3
ABCDEFGH
1NumberTotal MarksPoints AwardedRank9NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.54179948.5
762935662935
896924796924
99590.5389590.53
1015589.52915589.52
1110180110   
1210570011   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
 
Upvote 0
etaf said:
i'm sure there maybe a better way - other member may show

I'm assuming you are using 2019 version , as shown in your profile , so you do NOT have the filter function , which would make this much easier, and just use one function FILTER() to do it all, maybe with a sort

anyway

in F2and copy down

=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))

In G2 and copy down

=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")

In H2 and copy down

=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")

Book3
ABCDEFGH
1NumberTotal MarksPoints AwardedRank9NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.54179948.5
762935662935
896924796924
99590.5389590.53
1015589.52915589.52
1110180110   
1210570011   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
Click to expand...
Heya thanks for the reply! It’s saying #NUM!. Basically with the range A2:A12 that has 11 competitors, there may only be 5 competitors and it need it to work with that. Does it matter that there are other blank values or 0’s in the index?
 
Upvote 0
zeros in the index will also be counted
=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))

as that is using less than the number in e1
why is the rank showing zero, which this is using, so you may need to change the rank formula to not show a zero

what rank formula are you using

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.54179948.5
7629356   
8969247   
99590.538   
1015589.529   
1110180110   
1210570011   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")


OR

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.55179948.5
7629356   
8   
9   
10   
11   
12   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
 
Upvote 0
etaf said:
zeros in the index will also be counted
=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))

as that is using less than the number in e1
why is the rank showing zero, which this is using, so you may need to change the rank formula to not show a zero

what rank formula are you using

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.54179948.5
7629356   
8969247   
99590.538   
1015589.529   
1110180110   
1210570011   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")


OR

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.55179948.5
7629356   
8   
9   
10   
11   
12   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
Click to expand...
Thank you! Do you know how to do it so that if the number in E1 was 4, it would still show the first 5 results because the two competitors in 4th place tied so I need it to show both of them not just one?
 
Upvote 0
that should show as is , as we are using rank , below the number in the cell , so if you had 12 entries all below the number it will still show
its not counting just 5 , its using the ranking below or equal to 5

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984941939849
4156972441569724
598948.5498948.5
6179948.561939849
76293561569724
81939849498948.5
915697244   
1098948.54   
11179948.56   
12629356   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
 
Upvote 0
etaf said:
that should show as is , as we are using rank , below the number in the cell , so if you had 12 entries all below the number it will still show
its not counting just 5 , its using the ranking below or equal to 5

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984941939849
4156972441569724
598948.5498948.5
6179948.561939849
76293561569724
81939849498948.5
915697244   
1098948.54   
11179948.56   
12629356   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
Click to expand...
Why does it repeat in row 6 score 98
 
Upvote 0
because i copied and pasted the results , just to show an example of lots of 4's

ignore that - this is using your example with 2 4's
and anything below or equal to 5 ranking

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.54179948.5
7629356   
8969247   
99590.538   
1015589.529   
1110180110   
1210570011   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
 
Upvote 0
etaf said:
because i copied and pasted the results , just to show an example of lots of 4's

ignore that - this is using your example with 2 4's
and anything below or equal to 5 ranking

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank5NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.54179948.5
7629356   
8969247   
99590.538   
1015589.529   
1110180110   
1210570011   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
Click to expand...
Thank you. I should’ve specified. I meant if E1 was 4 not 5, there still should be 5 competitors showing because the last 2 got the same score of 8.5
 
Upvote 0
have you tried the formula at all and tested , it will do that

Book7
ABCDEFGH
1NumberTotal MarksPoints AwardedRank4NumberTotal MarksPoints Awarded
216399100116399100
3193984921939849
4156972431569724
598948.5498948.5
6179948.54179948.5
7629356   
8969247   
99590.538   
1015589.529   
1110180110   
1210570011   
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=IF(IFERROR(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1),"")=0,"",(INDEX($A$2:$A$21,SMALL(IF($D$2:$D$21<=$E$1,ROW($F$2:$F$21)),ROW(1:1))-1,1)))
G2:G12G2=IFERROR(INDEX($B$2:$B$12,MATCH(F2,$A$2:$A$12,0)),"")
H2:H12H2=IFERROR(INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Percentile Numbers dont add up...
Replies
1
Views
137
Eric W
Eric W
N
  • Solved
Duplicates question and scoring system
Replies
6
Views
267
MountainFog
M
NoelD
  • Question
Score sheet with class rankings
Replies
3
Views
117
NoelD
NoelD
N
  • Question
Auto Scoring Matrix
Replies
4
Views
242
Eric W
Eric W
A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
132
Anthony47
A

Forum statistics

Threads
1,215,069
Messages
6,122,951
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top