Compare Rank formula?

[bweisman12]

Hello,
I am to create a rank comparison formula. Can somone create a formula that tells me how many ranks a certain store has moved up or moved down?

Example: Store has a decreased rank of 2 (from 1 -> 3).

See below.


[TABLE="width: 500"]
<tbody>[TR]
[TD]STORE NAME[/TD]
[TD]RANK 2018[/TD]
[TD]STORE NAME[/TD]
[TD]RANK 2019[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD]1[/TD]
[TD]Store D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD]2[/TD]
[TD]Store B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD]3[/TD]
[TD]Store A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Store D[/TD]
[TD]4[/TD]
[TD]Store C[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	STORE NAME	RANK 2018		STORE NAME	RANK 2019	Movement
2	Store A	1		Store D	1	Up 3
3	Store B	2		Store B	2	No change
4	Store C	3		Store A	3	Down 2
5	Store D	4		Store C	4	Down 1
Sheet4

Cell Formulas
Range	Formula
F2	=TEXT(E2-VLOOKUP(D2,$A$2:$B$5,2,0),"""Down ""#;""Up ""#;""No change""")

 

[bweisman12]

Thank you!!

 

[bweisman12]

Try:

	A	B	C	D	E	F
Up 3
No change
Down 2
Down 1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]STORE NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]RANK 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]STORE NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]RANK 2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Movement[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=TEXT(E2-VLOOKUP(D2,$A$2:$B$5,2,0),"""Down ""#;""Up ""#;""No change""")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

What if instead of a count difference and I wanted a sum difference? Let's say column B & E are sales, not rank. Thanks!

 

[Eric W]

Easiest way is just to reverse the subtraction:

=TEXT(VLOOKUP(D2,$A$2:$B$5,2,0)-E2,"""Down ""#;""Up ""#;""No change""")

You can get a little fancier and add a $ sign:

=TEXT(VLOOKUP(D2,$A$2:$B$5,2,0)-E2,"""Down $""#;""Up $""#;""No change""")

and if you want cents as well:

=TEXT(VLOOKUP(D2,$A$2:$B$5,2,0)-E2,"""Down $""0.00;""Up $""0.00;""No change""")