ranking cells

[josh101287]

i have a worksheet where i have people listed with two different sets of stats for the. I am trying to list their names in a seperate spot in order based on the ranking of their stats and if their is a tie use use the second set of data as a tie breaker. I would also like this to be updated automatically. Thanks in advance.

[josh101287]

also there are times i may have blank cells so I would need it rank correctly with the blank cells in the mix.

[Pete_UK]

You can see in the attached workbook that I have used columns E to H to derive an effective tie-break value for speed and for errors (coloured blue), and used that to produce the ranks. Then it is a simple INDEX/MATCH to produce the names in the correct order.

Hope this helps.

Pete

[josh101287]

when i try ranking the the information on the actual real workbork i am using i do not get a correct ranking. there are some blank cells which may be a factor but I can't figure out whats going wrong. here is a copy of the actual data instead of made up data.It includes what they should be ranked to the right of the columns and the ranking the function gave me seperate,

[Pete_UK]

You need to fix the size of your range by using $ symbols in the range reference. Also, you need to remove the error message for blank cells. So, put this formula in I4:

=IF(D4="","",RANK(D4,$D$4:$D$11))

and this one in J4:

=IF(F4="","",RANK(F4,$F$4:$F$11,1))

then copy both down.

Hope this helps.

Pete

[josh101287]

thanks that works great now what do i need to have in the indexing formula so it disregards the blank cells

[josh101287]

i keep getting #N/A when i try to do the index match for the data

[Pete_UK]

Put the numbers 1 to 6 down column L, starting with L4.Then put this formula in M4:

=INDEX($C$4:$C$11,MATCH($L4,I$4:I$11,0))

Copy across into N4, then copy both formulae down.

Hope this helps.

Pete

[josh101287]

im pretty sure thats what i am doing here is the worksheet to show how i have it

[micope21]

Hi

Reason why you getting #N/A!! cos you using text instead using General! Formula don't work well with text!!

T5

Please Login or Register  to view this content.

copy down

See the file!

Regard
micope21

[josh101287]

awesome now it works great after i used that formula. i had noticed it was text instead of genral but i changed it and it still didnt do anything. But its all good now. Thanks everyone!!

[Pete_UK]

Glad you got your answer in the end - thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete