Ranking Changes in Quantity (whether they are positive or negative)

[ellison]

Hi, we are trying to rank changes in quantity.
Some of the differences may be positive and some may be negative (i.e. some of the qty's may go up, some may come down), but what we are interested in ranking is the actual difference (not whether it is +ve or -ve)
Some of the data contains blanks, which actually should be zero's (but that's on the next round of tidying up duties for me!)

Hopefully this makes it a little bit clearer....

excel-ranking-changes-in-qty-question.xlsx
	A	B	C	D	E	F	G
1	Qty-A	Qty-B	+ve or -ve	Difference	Rank		Manual Notes (not needed) Regarding the "difference between A & B is…"
2	50	50	same	0	5		...is 0, so this will be the smallest difference
3	5		positive	5	4		...is 5, which is the second smallest difference NB some of the figures are blank (and blanks represent a "0")
4	100	50	positive	50	1		...is "50" which is the largest, therefore ranked highest = Number 1
5	85	45	positive	40	2		...is "40" which is the 2nd largest, therefore rank = 2
6	35	75	negative	-40	2		is "still 40" (regardless of it is positive or negative) which is the equal 2nd largest, therefore rank = 2
7	35	65	negative	-30	3		Difference between A & B is "30" which is the equal 3rd largest, therefore rank = 3
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(D2>0,"positive",IF(D2<0,"negative","same"))
D2:D7	D2	=+A2-B2

Huge thanks for taking a look!






Huge thanks for

 

[Legacy 143009]

Thanks Peter, you helped me to question myself. OK, this is my final suggestion. I sometimes feel stupid when I don't come up with a simple solution like this on the first try

=RANK(D2,$D$2:$D$9,0)+COUNTIF($D$1:D1,D2)

 

[Peter_SSs]

Thanks Peter, you helped me to question myself. OK, this is my final suggestion. I sometimes feel stupid when I don't come up with a simple solution like this on the first try

=RANK(D2,$D$2:$D$9,0)+COUNTIF($D$1:D1,D2)

That does not rank the equal differences equally. Remember ..

Where the difference 40 occurs twice, they are both (rightly!) ranked number 2.

The accepted solution above ranks them like this

23 11 30.xlsm
	D	E
1	Difference	Rank
2	0	5
3	5	4
4	50	1
5	40	2
6	40	2
7	30	3
8	50	1
9	50	1
Rank ABS (2)

Cell Formulas
Range	Formula
E2:E9	E2	=COUNT(UNIQUE(FILTER(D$2:D$9,D$2:D$9>D2,"")))+1

 

[Legacy 143009]

Ohh, I was living in a complete different wold. Now I see.. Distrubute equally but continue to rank where it left. Oh sorry so much again

 

[Peter_SSs]

Ohh, I was living in a complete different wold. Now I see.

@ellison
I don't know if it is of interest to you but you can achieve the rank results without actually needing the 'Difference' column and without needing to copy the top formula down. This formula in cell C2 will spill the other results down the column.

23 11 30.xlsm
	A	B	C
1	Qty-A	Qty-B	Rank Difference
2	50	50	5
3	5		4
4	100	50	1
5	85	45	2
6	35	75	2
7	35	65	3
Rank ABS (3)

Cell Formulas
Range	Formula
C2:C7	C2	=LET(d,ABS(A2:A7-B2:B7),BYROW(d,LAMBDA(r,COUNT(UNIQUE(FILTER(d,d>r,"")))+1)))
Dynamic array formulas.

 

[ellison]

Wow that really is great...........

I'll swap over the solution to the one above in case anybody else wants to make use of it too

Huge thanks

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.