Forum Discussion
ajl_ahmed
Jul 27, 2022Iron Contributor
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 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
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,""))
3 Replies
Sort By
- Harun24HRBronze Contributor
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,""))
- ajl_ahmedIron ContributorMany thanks
- mtarlerSilver Contributor
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.