Rank a number closest to zero, but rank positive numbers higher

[mrmlg1980]

Hi, i am trying to write a rank formula that ranks the percentage closest to zero. For example, i have +0.1%, -0.1%, +0.2% & -0.2%. The number closest to zero is +0.1% & -0.1%, but the positive number is better, so 1st should be +0.1%, 2nd -0.1%, 3rd +0.2%, 4th -0.2%. I can rank closest to zero using ABS formula, but i can't get the formula to then treat the positive number as a higher ranking. Any ideas?? Thanks!

 

[kvsrinivasamurthy]

If A2:A5 is data range formula is

=INDEX(A$2:A$5,AGGREGATE(15,6,ROW($A$2:$A$5)/((ABS($A$2:$A$5)-(($A$2:$A$5>0)*(10^-6*ROW($A$2:$A$5))))=AGGREGATE(15,6,ABS($A$2:$A$5)-(($A$2:$A$5>0)*(10^-6*ROW($A$2:$A$5))),ROWS($C$2:$C2))),1)-ROW($C$1))

copy down.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mrmlg1980]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Excel for MS 365! Thank you

 

[Fluff]

Thanks for that.
How about

=XMATCH(ABS(A2),SORT(ABS($A$2:$A$7)))+IF(A2<0,SIGN(COUNTIF(A:A,-A2)),0)

 

[mrmlg1980]

Thanks for that.
How about

=XMATCH(ABS(A2),SORT(ABS($A$2:$A$7)))+IF(A2<0,SIGN(COUNTIF(A:A,-A2)),0)

Amazing, works perfectly! Many thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kvsrinivasamurthy]

Simpler formula

=IFERROR(AGGREGATE(15,6,($A$2:$A$5)/((ABS($A$2:$A$5)-(($A$2:$A$5>0)*(10^-6*ROW($A$2:$A$5))))=AGGREGATE(15,6,ABS($A$2:$A$5)-(($A$2:$A$5>0)*(10^-6*ROW($A$2:$A$5))),ROWS($C$2:$C2))),1),"")

 

[Fluff]

@kvsrinivasamurthy what has that formula got to do with ranking the data?