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.

STORE NAME	RANK 2018	STORE NAME	RANK 2019
Store A	1	Store D	1
Store B	2	Store B	2
Store C	3	Store A	3
Store D	4	Store C	4

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula F2 =TEXT(E2-VLOOKUP(D2,$A$2:$B$5,2,0),"""Down ""#;""Up ""#;""No change""") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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""")