Combind these two formulas - How?

[Badvgood]

Can these 2 following formulas be combind into one?

In C2 there is this

=RANK(B2,$B$2:$B$15)

In D2 there is this

=C2&IF(OR(MOD(C2,100)={11,12,13}),"th",CHOOSE(MIN(5,RIGHT(C2)+1),"th","st","nd","rd","th"))

Regards BVG

[pike]

Try...

=RANK(B2,$B$2:$B$15)&IF(OR(MOD(RANK(B2,$B$2:$B$15),100)={11,12,13}),"th",CHOOSE(MIN(5,RIGHT(RANK(B2,$B$2:$B$15))+1),"th","st","nd","rd","th"))

[shg]

=RANK(B2,$B$2:$B$15)&IF(OR(MOD(RANK(B2,$B$2:$B$15),100)={11,12,13}),"th",CHOOSE(MIN(5,RIGHT(RANK(B2, $B$2:$B$15))+1),"th","st","nd","rd","th"))

Which is a good reason not to do it -- it takes two understandable formulas and makes one piece of mush that takes three times as long to evaluate.

On the edge of inscrutable, here's an alternative for D2 (not mine, but don't know who should be credited):

=C26 & MID("thstndrdth", MIN(9, 2 * RIGHT(C26) * (MOD(C26-11, 100) > 2) + 1), 2)

[DonkeyOte]

You can, replace each reference to C2 in D2 with the RANK function... the advantage of using separate cells is that you avoid needlessly repeating functions (RANK)
(ie more efficient to use two cells as opposed to one in this instance)