Forum Discussion
How to correct "#N/A" when using Rank function and Index function
I am trying to create somewhat of a scoreboard that eliminates the lowest ranking player per level. The problem is, sometimes the players have the same score and my formulas end up showing the them as #N/A. I have enclosed an image to better demonstrate my issue.
You will see in column E & F, because three players have a 0 score, the two at the bottom show up as #N/A. The same applies to the two players in Level2 that have a score of 10. I have enclosed the formulas I have been using to the right. Is there a way to correct this so that the names and their score, even though they are the same, show up? Or perhaps is there a better way to do this?
Thank you so much.
Any assistance will be very much appreciated.
2 Replies
=RANK(C2,$C$2:$C$8,0)+COUNTIF(C2:$C$8,C2)-1Does this formula return the intended result?
alternatively you could use:
=RANK(C2,$C$2:$C$8,0)+A2/100000for the rank and then:
=INDEX(B$2:B$8,MATCH(SMALL($H$2:$H$8,$A2),$H$2:$H$8,0))for the Leading and Points
Then format the RANK column to not show the decimal places and then you won't confuse people why the "same score" results in different "rank"
or in newer versions of Excel you can just use:
=SORT(B2:.C8,2,-1)and it will spill out all the leaders and points in order (don't need to adjust the Rank at all)
