Ordinal number

[an]

Hello!

I need a formulae to write the ordinal number in next cell
of the value.
Ex:

14...2
19...4
13...1
15...3

Is it possible with Excel?
Thanks in advance.
an

 

[EZ Money]

Try the RANK worksheet formula.

 

[Leo Heuser]

Hello

Assuming numbers in B13:B16,
enter in C13

=RANK(B13,$B$13:$B$16,1)

Copy C13 to C16 with the fill handle (the little
square in the lower right corner of the cell)

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

 

[an]

Thanks, but the formulae don't locate 1st value, and
repeat many values.
The min value is 6 (Where are 1st to 5th?)

an

 

[an]

Oops!
Sorry. I forgot one $.
But I have zero values. Then, the formulae start count in
71 because I have 70 rows with "0".

In adition, it count in reverse order: The MAX value is
the major ordinal.

Thanks in advance.
an

 

[Leo Heuser]

You're welcome, but you have changed the
rules from your original posting

Clearly the *smallest* number is 1

LeoH

 

[Guest]

If you are not afraid of large formulas you can also concatenate the ordinal
suffix to the rank, i.e: 1st, 2nd, 3rd, etc., as in the following example
where the range C2:C27 is ranked.

=RANK(C2,$C$2:$C$27,1)&IF(RIGHT(RANK(C2,$C$2:$C$27,1),2)="11","th",IF(RIGHT(
RANK(C2,$C$2:$C$27,1),2)="12","th",IF(RIGHT(RANK(C2,$C$2:$C$27,1),2)="13","t
h",IF(RIGHT(RANK(C2,$C$2:$C$27,1),1)="1","st",IF(RIGHT(RANK(C2,$C$2:$C$27,1)
,1)="2","nd",IF(RIGHT(RANK(C2,$C$2:$C$27,1),1)="3","rd","th"))))))

It is important note that the RANK value has to be evaluated for 11, 12, and
13 before it is evaluated for 1, 2 and 3 in the If statements.


HTH

Steve H

 

[an]

Exactly.
Many thanks.
an

-----Original Message-----
If you are not afraid of large formulas you can also concatenate the ordinal
suffix to the rank, i.e: 1st, 2nd, 3rd, etc., as in the following example
where the range C2:C27 is ranked.

=RANK(C2,$C$2:$C$27,1)&IF(RIGHT(RANK(C2,$C$2:$C$27,1),2) ="11","th",IF(RIGHT(
="3","rd","th"))))))

It is important note that the RANK value has to be evaluated for 11, 12, and
13 before it is evaluated for 1, 2 and 3 in the If statements.


HTH

Steve H




.

 

[an]

Sorry, but why?
I think was correct.

Cordialy,
an

 

[Leo Heuser]

My answer hadn't reached the server 2 hours after
I posted it, so it may be lost somewhere in cyber space

I'll try again:

------------------------------

Not that it matters, as you apparently have got, what you wanted,
which is the important thing , but in your original posting you
ranked the numbers from smallest to largest,

14...2
19...4
13...1
15...3

13 was ranked 1, 14 was ranked 2, 15 was ranked 3 and 19
was ranked 4. The above list will be obtained from my original
formula.

If you change 1 to 0 in my formula as stated, the numbers will
be ranked from largest to smallest, so 19 is ranked 1, 15 is ranked 2,
14 is ranked 3 and 13 is ranked 4 or
14...3
19...1
13...4
15...2

Since this list isn't similar to the original list, my comment
about you having changed the rules.