1

I am trying to create some sort formulas on excel.

An example is shown below regarding players and their corresponding scores. I need to query the "Raw Data" and get the "Top 3" players and their scores.
enter image description here

I've figured out how to populate the information however I don't know how to deal when there are two points that are the same (i.e. James and Janet). I've read that I have to use countif() but I can't figure out how to make it work.

Improve this question

1 Answer 1

Reset to default
1

Easiest solution is probably to add a helper column where you calculate the rank of each player.

To make formulas auto-updating when you add new items, and also making them more readable I suggest to convert your raw data range to list (insert - table).

  • the formula for rank:
    =RANK.EQ([@points],[points])+COUNTIF($F$2:F2,[@points])
  • the formula to get player names and points (same formula in both columns):
    =INDEX(Table1[[players]:[points]],MATCH($A3,Table1[rank],0),COLUMN()-1)

enter image description here

Of course you can do it also without helper column but that would make your formula in column B much more complicated.

Update

Formula without helper column:

=OFFSET(Table2[[#Headers],[players]],SMALL(IF(Table2[points]=C3, ROW(Table2[points]),""), COUNTIF($C$3:C3,C3))-2,0)

This is an array formula, you need to enter it with CTRL+SHIFT+ENTER.

Improve this answer
1
  • I need to gather this information on a monthly basis and share with others. In order to make a less complicated spreadsheet I would like to avoid adding extra columns/info. Would you be able to help me with formulating the formula for column B without a helper column? Much appreciated!
    – TLC
    Commented Oct 18, 2015 at 2:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .