Ho to RANK Non-Continuous Range In Excel

[SollyEng]

Hi

I am trying to RANK a range of cells that are not next to each other.

Thanks

 

[bines53]

Hi

Upload a file, and specify the expected results.


David

 

[Peter Bartholomew]

The standard RANK function is rather more flexible than one might expect. If 'firstRange' and 'secondRange' are two named ranges then

{= RANK.AVG(firstRange,(firstRange,secondRange),1)}

will rank each number within the first array as a member of the union of the two ranges. A test on

ISNUMBER(firstRange)

might improve the look of the output.

 

[SollyEng]

Please see attached file. I am trying to compare different sites (site 1, site 2, etc) against each other in terms of the CPC (e.g. E17 compared and ranked against H17, K17, O17, Q17 and T17). I am looking for a more elegant solution that the current formula used.

 

[SollyEng]

The standard RANK function is rather more flexible than one might expect. If 'firstRange' and 'secondRange' are two named ranges then

{= RANK.AVG(firstRange,(firstRange,secondRange),1)}

will rank each number within the first array as a member of the union of the two ranges. A test on

ISNUMBER(firstRange)

might improve the look of the output.

Thanks! I am trying to get away from named ranges.

 

[AliGW]

In F17:

=COUNTIFS($E$12:$T$12,"CPC",$E17:$T17, "<"&E17)+1

Copy and paste to the other cells where the ranks need calculating.

 

[SollyEng]

Thanks! I cannot get it to work?

 

[AliGW]

Did you look at the attachment which demonstrates it working?

What exactly do you mean by "I cannot get it to work"? Wrong answers? Error message? What???

 

[SollyEng]

Sorry, my mistake! It is working perfectly! Thank you so much for solving my little problem.

 

[AliGW]

No problem - glad to help!

 

[Peter Bartholomew]

I can see your problem. It is not so much an issue with names as the fact that most objects of interest are distributed across sites and it is simply painful (not to mention error-prone) to have to keep on defining multi-area ranges. Sometimes the data has to be reorganised as a first step towards analysis.

In the attached I have define the columns used for CPC fields (I would probably use a macro to define and name such a multi-area range). Then intersection with the current row picks out the values to be ranked.

Not that I have a problem with Ali's use of COUNTIFS; unlike RANK, it works over text strings and can be extended to include multiple criteria (e.g. last name followed by first name).

 

[SollyEng]

Excellent! Thank you for the effort!