Modify formula to also return =1st, =2nd, =3rd, =4th

[score]

I have this formula =RANK(L29,(L29,T29,L49,T49))&LOOKUP(MOD(RANK(L29,(L29,T29,L49,T49)),5),{1,2,3,4;"st","nd","rd","th"}) that return 1st, 2nd, 3rd, 4th, it is possible to modify this formula to also return =1st, =2nd, =3rd, =4th if any of the values within L29,T29,L49,T49 are the same (tied)

Thanks in advance for any help.

[Debraj Roy]

Attach a sample workbook.. so that we can see.. where the other RANKIng are displayed..

Where do you want to display 1st, 2nd and so on..

[score]

Many thanks!! File attached,The formula will be in H15, P15, H35, P35 (blue stripe) and will make reference on cells L29,T29,L49,T49 (blue cells)

[newdoverman]

I don't know what is in your file but it sets off Office security warnings that are not just cautions about macros or content from the web.

[Glenn Kennedy]

NDM - I often see this with .xls extensions. Is Excel defaulting to protected mode?

[Debraj Roy]

I Have used 4 different Formula for all sections..

check Each one carefully..

• H 15 > =RANK(L29,($L$29,$T$29,$L$49,$T$49))+SUM(COUNTIF(INDIRECT({"T29","l49","t49"}),L29))
• P 15 > =RANK(T29,($L$29,$T$29,$L$49,$T$49))+SUM(COUNTIF(INDIRECT({"l49","t49"}),T29))
• H 35 > =RANK(L49,($L$29,$T$29,$L$49,$T$49))+SUM(COUNTIF(INDIRECT({"t49"}),L49))
• P 35 > =RANK(T49,($L$29,$T$29,$L$49,$T$49))

I hope you can manage the "St-Nd- part"

[newdoverman]

NDM - I often see this with .xls extensions. Is Excel defaulting to protected mode?

I'm always in protected view when I have downloaded a file from the web. I have downloaded many xls files without problem. This is the first time that I got an "in your face" warning about it in bright red. The usual warnings are in yellow and are about links or macros.

[Glenn Kennedy]

Needless to say, I haven't downloaded the attachment...

[score]

Hi Debra Roy, thank you for your help but i can't manage the "1st,2nd,3rd,4rth or =1st,=2nd,=3rd,=4th, im not that good on excel.

Regards.

[daddylonglegs]

If there are only 4 values you can use this formula for ranking L29

=IF(OR(L29=T29,L29=L49,L29=T49),"=","")&CHOOSE(RANK(L29,(L29,T29,L49,T49)),"1st","2nd","3rd","4th")

That will give you = if it's equal to any of the three other values, adjust for L49, T29 and T49

[score]

Hi Glenn Kennedy, sorry about that, but i don't know why the warning on the file, i have uploaded it before and been downloaded with no warning from anyone, anyway i understand your position and really appreciate your intentions to help.

Regards

[score]

Thank you daddylonglegs, is no way to give =1st,=2nd,=3d,=4th and not just = if it's equal to any of the other three values?

[score]

Hi newdoverman, sorry about that, but i don't know why the warning on the file, i have uploaded it before and been downloaded with no warning from anyone, anyway i understand your position and really appreciate your intentions to help.

Regards

[daddylonglegs]

......is no way to give =1st,=2nd,=3d,=4th and not just = if it's equal to any of the other three values?

Yes, that what it does - the formula returns 1st, 2nd, 4rd or 4th as previously but it will also give you an = with that, e.g. =1st, if there are matching values.

Try it out and see if it does what you want........

[score]

Way to go daddylonglegs, wonderful, thank you very much, it does what iwas lookin for, Cheers!!!

Yes, that what it does - the formula returns 1st, 2nd, 4rd or 4th as previously but it will also give you an = with that, e.g. =1st, if there are matching values.

Try it out and see if it does what you want........

[score]

Thanks to everyone who helped me on this thread, really appreciated!!!