Using IF to return the text name of a cell with a certain rank

[greykitten]

Hi,

I need to use the IF formula to return the name of a cell that has a certain rank. I have ten cells with text words in them and each has a rank 1-10. And I need the names to populate in a column in order of rank. I can't figure out how to write this in the syntax. I've uploaded the file where I'm trying to do this (see link below). Thank you!!


https://hotfile.com/dl/215673014/3706707/RANK_Practice.xlsx.html

 

[SirJB7]

Hi, greykitten!

Tried this?

=INDICE(B$4:B$13;E4) -----> in english: =INDEX(B$4:B$13,E4)

placed in column F at same row of column E data.

Regards!

 

[greykitten]

Dear SirJB7,

Thank you so much! That worked great. If I had a larger list of items, say 100, how would I write it so that only items ranked 1-10 would appear? Rank<10? Last thing, is there a way to arrange the text items in descending order by their respective rank value??


Thanks again,

greykitten

 

[SirJB7]

Hi, greykitten!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Using%20IF%20to%20return%20the%20text%20name%20of%20a%20cell%20with%20a%20certain%20rank%20-%20RANK%20Practice%20%28for%20greykitten%20at%20chandoo.org%29.xlsx


You haven't considered tied products (same Count), so I've added a few lines, 4) and the formulas are these:


B4: Your product name list


C4: Your count name list


E4: Your rank formula

=JERARQUIA(C4;$C$4:$C$17;1) -----> in english: =RANK(C4,$C$4:$C$17,1)

note the repetition of 4 and the absence of 5-6-7


F4: My updated rank formula

=E4+CONTAR.SI(E$4:E4;E4)-1 -----> in english: =E4+COUNTIF(E$4:E4,E4)-1

note all the values


G4: Name of product with rank as F

=INDICE(B$4:B$17;COINCIDIR(FILA()-3;F$4:F$17;0)) -----> in english: =INDEX(B$4:B$17,MATCH(ROW()-3,F$4:F$17,0))


I4: Pos. no.

=SI(FILA()-3<=J$1;FILA()-3;"") -----> in english: =IF(ROW()-3<=J$1,ROW()-3,"")


J1: 12 (no. of Top-N wanted)


J4: Name of product in rank I

=SI(FILA()-3<=J$1;G4;"") -----> in english: =IF(ROW()-3<=J$1,G4,"")


Regards!

 

[SirJB7]

Hi, greykitten!


Regarding your almost cross-posted topic:

http://chandoo.org/forums/topic/using-rank-formula-to-find-top-ten-largest-values

download again the updated file from same previous link.


Additions:


K4: Name of product in descending rank

=SI(FILA()-3<=J$1;INDICE(B$4:B$17;COINCIDIR(K.ESIMO.MAYOR(F$4:F$17;FILA()-3);F$4:F$17;0));"") -----> in english: =IF(ROW()-3<=J$1,INDEX(B$4:B$17,MATCH(LARGE(F$4:F$17,ROW()-3),F$4:F$17,0)),"")


Regards!