Rankings

[Fredek]

Hi guys,

I have Peer Group values e.g. 1,4,8,10.

I would like all non peer groups records to be ranked by the scale above, e.g. 0.5 to have a rank 1, 9 to have a rank 4 etc. Rank 8 can have rank 3/4, for this purpose it's not crucial.

Is there a neat way of doing this?

 

[Eric W]

Maybe:

=MATCH(A1,{0,1,4,8,10})

 

[Fredek]

Hi Eric,

Thank you very much for your help! How can I do this with an array?

My array contains many numbers that are all over the place, not ascending/descending.

Kind regards,
Fred

 

[Eric W]

It's easy enough to convert that formula to use a range on the sheet:

=MATCH(A1,$Z$1:$Z$5)

where Z1:Z5 = 0,1,4,8,10.

The bigger problem is that they're not ascending/descending. MATCH requires that. Can you sort it? If not, can you post your array? There might be some options.

 

[Fredek]

Hi Eric,

I have 60 odd values in column I. Column J shows the current rank of values in column I.

I would like to be able to see what impact a 10% change to the value would have on it's rating keeping everything else constant.

The below works when the records are sorted manually. Is there a way to do this without the need to sort?

=Match(I2*1.1,$I$2:$I$62,1)

 

[Eric W]

Maybe:

=SUM(IF(IF(ROW($I$2:$I$62)=ROW(),I2*1.1,$I$2:$I$62)>I2*1.1,1))+1
confirmed with Control+Shift+Enter.

 

[Fredek]

Amazing, thank you very much!

 

[Eric W]

You're welcome.