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]

Hi,

D2 will be:

=ABS(A2-B2)

E2 will be:

=RANK(D2,$D$2:$D$7,0)

 

[ellison]

Absolutely brilliant!
Just one fly in the ointment left...

In terms of the rankings:

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

But the next biggest difference is 30, and we'd like that to be ranked number 3 (rather than number 4).

Is there are a way to deal with that?

 

[Phuoc]

Try this

Book1
	A	B	C	D	E
1	Qty-A	Qty-B	+ve or -ve	Difference	Rank
2	50	50	same	0	5
3	5		positive	5	4
4	100	50	positive	50	1
5	85	45	positive	40	2
6	35	75	positive	40	2
7	35	65	positive	30	3
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(D2>0,"positive",IF(D2<0,"negative","same"))
D2:D7	D2	=ABS(A2-B2)
E2:E7	E2	=1+SUMPRODUCT(($D$2:$D$7>D2)/COUNTIF($D$2:$D$7,$D$2:$D$7))

 

[Peter_SSs]

Another way

23 11 30.xlsm
	A	B		D	E
1	Qty-A	Qty-B		Difference	Rank
2	50	50		0	5
3	5			5	4
4	100	50		50	1
5	85	45		40	2
6	35	75		40	2
7	35	65		30	3
Rank ABS

Cell Formulas
Range	Formula
D2:D7	D2	=ABS(A2-B2)
E2:E7	E2	=COUNT(UNIQUE(FILTER(D$2:D$7,D$2:D$7>D2,"")))+1

 

[ellison]

Hi all, huge thanks for working out those brilliant solutions....
And for what it's worth both Peter's and Phuocs answers both work.
(And a big thank you to Flashbond too!)

 

[Legacy 143009]

Eh heheh it is a bit sketchy but it works

=IF(MOD(ROW(),2)=0,CEILING(RANK.AVG(D2,$D$2:$D$7,0),1),FLOOR(RANK.AVG(D2,$D$2:$D$7,0),1))

 

[Peter_SSs]

Eh heheh it is a bit sketchy but it works

Not if the previous ones do.
Check rows 5 & 6 and ..

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

 

[Legacy 143009]

Hi Peter

Not if the previous ones do.
Check rows 5 & 6 and ..

It was working perfectly fine for me. I use RANK.ORT function. Maybe my translation to English was wrong. Turkish ORT is ORTALAMA which means AVERAGE in English. RANK.AVG was the closest match I could find. I have no chance to test with English function names.

Although, I found another flaw with my code. It returns the wrong result if both duplicates are in even-number rows.

 

[Peter_SSs]

It was working perfectly fine for me. I use RANK.ORT function. Maybe my translation to English was wrong.

Hmm, interesting. This is what I get when I use the formula from post 7. Rows 4 & 5 are correct but all others are 1 off.

23 11 30.xlsm
	D	E	F
1	Difference	Rank	Post #7
2	0	5	6
3	5	4	5
4	50	1	1
5	40	2	2
6	40	2	3
7	30	3	4
Rank ABS

Cell Formulas
Range	Formula
D2:D7	D2	=ABS(A2-B2)
E2:E7	E2	=COUNT(UNIQUE(FILTER(D$2:D$7,D$2:D$7>D2,"")))+1
F2:F7	F2	=IF(MOD(ROW(),2)=0,CEILING(RANK.AVG(D2,$D$2:$D$7,0),1),FLOOR(RANK.AVG(D2,$D$2:$D$7,0),1))