Ranking with Ties

[ellyzadg]

Hi Everyone,

Would like to ask correct formula on how to deal with ranking tie ups. Thank you in advance!

Below are my sample data and desired result

Employee ID	Sales Person	Sales
S00001 S00002 S00003 S00004 S00005 S00006 S00007 S00008 S00009 S00010 <tbody> </tbody>	Richard Tan Bernadeth Milante Isamael Gamba Grace Dineros Jenette Calderon Joseph Solidum Leny Patricio Marvin Delacroix Jace Balaren Liliana Vess <tbody> </tbody>	$ 45,441.00 $ 12,543.00 $ 45,441.00 $ 23,144.00 $ 12,354.00 $ 35,311.00 $ 42,645.00 $ 42,623.00 $ 19,820.00 $ 16,336.00 <tbody> </tbody>

<tbody>
</tbody>

Outcome

Rank	Sales	Salesperson (what i get)	Desired Result
1 2 3 <tbody> </tbody>	$ 45,441.00 $ 45,441.00 $ 42,645.00 <tbody> </tbody>	Richard Tan Richard Tan Leny Patricio <tbody> </tbody>	Richard Tan Ismael Gamba Leny Patricio <tbody> </tbody>

<tbody>
</tbody>

Thanks!

 

[ellyzadg]

Hi Everyone,

Would like to ask correct formula on how to deal with ranking tie ups. Thank you in advance!

Below are my sample data and desired result

Employee ID	Sales Person	Sales
S00001 S00002 S00003 S00004 S00005 S00006 S00007 S00008 S00009 S00010 <tbody> </tbody>	Richard Tan Bernadeth Milante Isamael Gamba Grace Dineros Jenette Calderon Joseph Solidum Leny Patricio Marvin Delacroix Jace Balaren Liliana Vess <tbody> </tbody>	$ 45,441.00 $ 12,543.00 $ 45,441.00 $ 23,144.00 $ 12,354.00 $ 35,311.00 $ 42,645.00 $ 42,623.00 $ 19,820.00 $ 16,336.00 <tbody> </tbody>

<tbody>
</tbody>

Outcome

Rank	Sales	Salesperson (what i get)	Desired Result
1 2 3 <tbody> </tbody>	$ 45,441.00 $ 45,441.00 $ 42,645.00 <tbody> </tbody>	Richard Tan Richard Tan Leny Patricio <tbody> </tbody>	Richard Tan Ismael Gamba Leny Patricio <tbody> </tbody>

<tbody>
</tbody>

*im using index match in salesperson column

Thanks!

 

[KennyGreens]

Hi, why not just sort the data largest to smallest by the sales amount? Maybe I'm missing something as well, but why would Richard Tan be rank 1 and 2? Wouldn't Isamael Gamba be either 1st or 2nd as well?

 

[ellyzadg]

Hi, why not just sort the data largest to smallest by the sales amount? Maybe I'm missing something as well, but why would Richard Tan be rank 1 and 2? Wouldn't Isamael Gamba be either 1st or 2nd as well?

Hi kenny, Richard and Ismael may interchange. Yes I can sort this , however our requirement is to provide a formula using index match and then consider when there are tie ups.

 

[FormR]

Hi, here is an option you can try..

Excel 2013/2016
	A	B	C
1	Employee ID	Sales Person	Sales
2	S00001	Richard Tan	45441
3	S00002	Bernadeth Milante	12543
4	S00003	Isamael Gamba	45441
5	S00004	Grace Dineros	23144
6	S00005	Jenette Calderon	12354
7	S00006	Joseph Solidum	35311
8	S00007	Leny Patricio	42645
9	S00008	Marvin Delacroix	42623
10	S00009	Jace Balaren	19820
11	S00010	Liliana Vess	16336
12
13
14
15	Outcome
16
17	Rank	Sales	Desired Result
18	1	45441	Richard Tan
19	2	45441	Isamael Gamba
20	3	42645	Leny Patricio
Sheet1

Cell Formulas
Range	Formula
B18	=LARGE($C$2:$C$11,A18)
C18	=INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($C$2:$C$11=B18),COUNTIFS(B$18:B18,B18)))

 

[steve the fish]

Heres a way. In B16:

=LARGE($C$2:$C$11,A16)

In C16 entered CTRL-SHIFT-ENTER:

=INDEX($B$2:$B$11,SMALL(IF($C$2:$C$11=B16,ROW($C$2:$C$11)-ROW($C$2)+1),COUNTIF($B$16:B16,B16)))

 

[Joe4]

I have merged your two threads together. In the future, please do not post the same question multiple times. Either edit the original post (I believe you have 10 minutes to do so), or reply to the original thread with your clarification.

Per forum rules, threads of a duplicate nature will typically be locked or deleted.

 

[ellyzadg]

I have merged your two threads together. In the future, please do not post the same question multiple times. Either edit the original post (I believe you have 10 minutes to do so), or reply to the original thread with your clarification.

Per forum rules, threads of a duplicate nature will typically be locked or deleted.

Noted and thanks. My apologies I did not noticed I doubled the thread I edited. Thank you.