Forum Discussion

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Jul 27, 2022
Solved

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

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 ...
excel
Formulas and Functions
office 365
  • Harun24HR's avatar
    Harun24HR
    Jul 28, 2022

    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,""))

     

mtarler's avatar
mtarler
Silver Contributor
Jul 27, 2022

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.

 

Resources