SOLVED

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

3 Replies

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

@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

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

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

Many thanks