Rank table highest positive to lowest positive first, then lowest negative to highest negative.

[Mr_Stern]

Hi, I'm looking for a formula to use in a table ranking values.
It's a bit of an odd ranking that I can't manage to get quite right.

The values have both positive and negative numbers.
It would need to rank highest positive to lowest, then Lowest to highest negative.

E.g.
rank value
1) 20
2) 15
3) 10
4) -40
5) -12
6) -2

Kind regards

 

[hagia_sofia]

Hello, you might test:

=LET(
a,A1:A6,
SORTBY(a,IF(a>=0,1,2),1,ABS(a),-1))

 

[Mr_Stern]

Hello, you might test:

=LET(
a,A1:A6,
SORTBY(a,IF(a>=0,1,2),1,ABS(a),-1))

Thanks but not quite what I'm after. The formula does well in sorting the values accordingly, but I need it to assign a value to the numbers. I suppose how I presented the question was confusing. Below is a better example
rank value
2) 15
4) -40
6) -2
1) 20
5) -12
3) 10

 

[hagia_sofia]

Ah, I see, my bad then. The question remains how to handle ties - see how this will work:

=LET(
a,A1:A6,
r,SEQUENCE(ROWS(a)),
s,SORTBY(a,IF(a>=0,1,2),1,ABS(a),-1),
XLOOKUP(a,s,r))

 

[Peter_SSs]

Does this do what you want?

(BTW, can your data include zero values? If so, how/where should they be ranked?)

25 03 20.xlsm
	A	B
1	No.	Rank
2	15	2
3	-40	4
4	-2	6
5	20	1
6	-12	5
7	10	3
Rank

Cell Formulas
Range	Formula
B2:B7	B2	=LET(d,A2:A7,a,ABS(d)+IF(d>0,10^9),XMATCH(a,SORT(a,,-1)))
Dynamic array formulas.