Rank function "tiebreaker"

Hello.

I have a RANK function instaled in my tool. It works ok, but recently a "tie" occurred between two values.

Sales 2014 YTD Sales 2013 rank
125.036,00 € 302.451,00 € 7
152.006,00 € 295.324,00 € 2
136.542,00 € 312.574,00 € 4
140.235,00 € 225.634,00 € 3
178.245,00 € 322.654,00 € 1
136.542,00 € 291.475,00 € 4
129.642,00 € 310.250,00 € 6

I use =RANK(A2;$A$2:$A$8) formula, which returns rank 4 twice. How can I use 'Sales 2013' to get a "tiebreaker"?

I don't know the RANK function, but you can make and rank a new column that is Sales 2014 + .000001 * sales 2013.

Originally Posted by azumi

Maybe just use 2014 as tie breaker:

=RANK(A2;$A$2:$A$8)+COUNTIF(A$2:A2;A2)-1

I don't understand how countif breaks a tie ... Am I missing something obvious?

Edit: Oh, I see what you tried. I can't use this, I actualy want 2013 to be a factor if 2014 is equal.

Try this...

Data Range

	A	B	C
1	2014	2013	Rank
2	125,036	302,451	7
3	152,006	295,324	2
4	136,542	312,574	4
5	140,235	225,634	3
6	178,245	322,654	1
7	136,542	291,475	5
8	129,642	310,250	6

This formula entered in C2 and copied down:

=RANK(A2;A$2:A$8)+SUMPRODUCT(--(A2=A$2:A$8);--(B2<B$2:B$8))

Originally Posted by glosos

This can interfere with the original ranking, if they are very close to each other. I solve nothing by doing that

It doesn't interfere. Just make the .000001 small enough.