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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

D
  • Solved
Using COUNTIFS to Rank Within Nested IF, THEN
Replies
4
Views
563
diderooy
D
R
  • Question Question
Percentile Numbers dont add up...
Replies
1
Views
279
Eric W
Eric W
N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
249
Nicha
N
S
  • Solved
Split numbers in one column to multiple columns
Replies
3
Views
232
sharshra
S
ibmy
  • Solved
Count number of occurence of value with condition
2
Replies
10
Views
275
Alex Blakenburg
A

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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