1st, 2nd, 3rd places or more than one in either position.

[Detectiveclem]

Hi,

I am using the following formula to place individuals 1st, 2nd or 3rd depending upon their score.

=IFERROR(OFFSET($G5,MATCH(LARGE(K5:K17,3),K5:K17,0)-1,0),"")

All works well, until I have two or more people with the same top score, I.E. 1st place is Annabel with 45, then John with 43 is second, however if Adnan also has 43 points, then my formula simply show Annabel is 1st and then John as 2nd and also 3rd place, no mention of Adnan. Can anyone assist me so that I have simply 1st and 2x 2nds (these seconds could simply be shown together) in this scenario and then a 3rd with the next score down (or wherever the the duplication appears).

I hope I have explained my issue

 

[smitpau]

If you use Office 365 try the Index / Sort / Sequence functions, this link should give hopefully the necessary info.

How to calculate Top 10 with formulas in Excel

In this post we look at calculating a Top 10 list this with traditional and dynamic array formulas, and with selection criteria.

exceloffthegrid.com

Let me know if it doesn't work though.

Thanks,

 

[Detectiveclem]

If you use Office 365 try the Index / Sort / Sequence functions, this link should give hopefully the necessary info.

How to calculate Top 10 with formulas in Excel

In this post we look at calculating a Top 10 list this with traditional and dynamic array formulas, and with selection criteria.

exceloffthegrid.com

Let me know if it doesn't work though.

Thanks,

Hi Smit-au,

Thanks for assisting, but not sure how I would write such a formula, having never used Index/Sort/Sequence. I am using Office 365.

Can you assist please?

 

[DanteAmor]

I'm not sure how you have your data.
But assuming the following, we have a triple tie in second and also another tie; and all names are displayed:

Dante Amor
	A						G				K	L	M
4							Name				Score		Name
5							Ana				43		Fra
6							Ben				40		Ism
7							Car				43		Car
8							Dan				20		Ana
9							Ern				19		Ben
10							Fra				50		Noe
11							Gus				15		Mia
12							Hec				18		Len
13							Ism				43		Dan
14							Jho				19		Jho
15							Len				21		Ern
16							Mia				25		Hec
17							Noe				38		Gus
Hoja5

Cell Formulas
Range	Formula
M5:M17	M5	=INDEX($G$5:$G$17,SUMPRODUCT((LARGE($K$5:$K$17+(ROW($K$5:$K$17)/1000),ROWS(N$5:N5))=$K$5:$K$17+(ROW($K$5:$K$17)/1000))*(ROW($K$5:$K$17)))-4)

 

[Detectiveclem]

Thank you DantaAmor,

This doesn't quite achieve what I am trying to do. As you'll see when I have two groups with the same score the one on the top of the list when they share second place with another they appear twice rather than allowing the other person with the same score to be shown. The following screenshot will show you what the issue is.

 

[smitpau]

To use DanteAmor's example this code would work

=INDEX(SORT(C6:D18,2,-1),SEQUENCE(10))

In your case if you use Office 365 this should work if you drag it across the columns.

=INDEX(SORT($A$2:B$13,1,-1),SEQUENCE(3))

 

[Detectiveclem]

Thanks, but how should the formula look like in my case as the name range is G5:G17, the Scores are in each of the columns adjacent to Column G and my formal =IFERROR(OFFSET($G5,MATCH(LARGE(H5:H17,1),H5:H17,0)-1,0),"") appears in cells
H21 etc for 1st place and
=IFERROR(OFFSET($G5,MATCH(LARGE(H5:H17,2),H5:H17,0)-1,0),"")
for 2nd place in H22 etc and
=IFERROR(OFFSET($G5,MATCH(LARGE(H5:H17,3),H5:H17,0)-1,0),"")
for 3rd place in H23

Thanks again.

Paul

 

[smitpau]

Which version of Excel do you use?

 

[Detectiveclem]

Office 365 on Mac

 

[smitpau]

Try this in cell H21 and see if it works dragging across to the right again.

=INDEX(SORT($G$5:H$17,2,-1),SEQUENCE(3))

The 2 may need to change to 3 etc for the 3 column so I in this case.