SOLVED

Need a formula to write the rank of students as a text.

Contributor

I need a formula to write the rank of students as a text. such that:

-  If the student rank is (1), (cell-E2), then the rank will be FIRST (cell D2).

-  If the student rank is (1), (cell-E4), then the rank will be THird  (cell D4) and will be Third-Repeated in cell (D5) for the next student. while the rank of the student in cell (E6) will be the fifth as shown here.

is it possible to do that?

Thanks

3 Replies

@ajl_ahmed here is an option but you need to supply the word conversions:

=LET(in,E2:E14,
     r,RANK.EQ(in,in,1),
     SCAN(0,r,LAMBDA(prior,curr,
       LET(
         new,INDEX(counting[#Data],curr),
         IF(LEFT(new,LEN(prior))=prior,
            new&"-repeated",
            new)))))

this version and a version for older Excel are in the attached.

 

best response confirmed by ajl_ahmed (Contributor)
Solution

@ajl_ahmed 

Use a helping list of words equivalent of numbers. Then use below formula.

=IF(COUNTIF($E$2:$E2,E2)>1,XLOOKUP(E2,$I$2:$I$100,$J$2:$J$100,"")&"-repeated",XLOOKUP(E2,$I$2:$I$100,$J$2:$J$100,""))

Harun24HR_0-1658979575660.png