Dear Sir,

Ranking by MS-Excel Function

There is an array of figures shown as follows:
{1;1;1;4;4;4;8;8;8;8;8;8;13;13;13;16;16;16;20;20;2 0;22;22;25;25;27;27;27;27;30;30;30;30}

Say, the array is located at A1:A33.
Admittedly, the array has been subject to Data Sort.
Hopefully, it will well behave and offer clarity.
________________________________________
Would like to rank the figures such that,
1 (by virtue of the leading appearance) will be ranked 1 (quite obviously).
4 (where there are 3 instances) will (all) be ranked 2.
However, by applying Excel function, Rank (4, Array) gives 4.
Reason being that, duplication of the 1's at the forefront has given rise to,
Rank (1, 2nd appearance) yields 2.
Rank (1, 3rd appearance) yields 3.
________________________________________
Further requirement is to rank (8's) as 3; rank (13's) as 4 and rank (16's) as 5.
Last but not least, rank (30's) should be 10.
________________________________________
Please devise a formula to rank each element of the given array such that
the figures will be evaluated individually as per requirement (given above).

What if, the array is then rearranged with figures in any other order,
will the formula hold true still?

Please share your experience and results. Regards.
________________________________________

Hi Ivan,

Am Sat, 24 Jan 2015 01:13:24 -0800 (PST) schrieb Ivan Peeler:

{1;1;1;4;4;4;8;8;8;8;8;8;13;13;13;16;16;16;20;20;2 0;22;22;25;25;27;27;27;27;30;30;30;30}

Say, the array is located at A1:A33.

Further requirement is to rank (8's) as 3; rank (13's) as 4 and rank (16's) as 5.
Last but not least, rank (30's) should be 10.
________________________________________
Please devise a formula to rank each element of the given array such that
the figures will be evaluated individually as per requirement (given above).

What if, the array is then rearranged with figures in any other order,
will the formula hold true still?

if column A is sorted ascending try in B1:
=RANK(A1,$A$1:$A$33,1)
and in B2:
=IF(A2=A1,B1,B1+1)
and copy down

Is column A sorted descending try in B1:
=RANK(A1,$A$1:$A$33,0)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Ivan,

Am Sat, 24 Jan 2015 10:42:12 +0100 schrieb Claus Busch:

if column A is sorted ascending try in B1:
=RANK(A1,$A$1:$A$33,1)
and in B2:
=IF(A2=A1,B1,B1+1)
and copy down

Is column A sorted descending try in B1:
=RANK(A1,$A$1:$A$33,0)

if you use a helper column sorting does not matter.

Please have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Rank"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch Esq.,

Thank you for responding to my query.

Regards.