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

 

[Detectiveclem]

Hi, just tried what you suggest and get the following error message #SPILL!

 

[smitpau]

Can you upload your spreadsheet to Dropbox (or equivalent) and put a link to it as it will be easier if I could open it and have a play to see what works.

Obviously please remove any sensitive information first.

 

[DanteAmor]

The following screenshot will show you what the issue is.

You can try to put the example with the XL2BB tool, from the image we cannot copy the data.

 

[smitpau]

Hi, just tried what you suggest and get the following error message #SPILL!

Makes sense, if you delete H22, H23 the Spill error should go away as it's stopping the dynamic array from being populated.

 

[Detectiveclem]

Hopefully this will work

Top 3.xlsx

Shared with Dropbox

www.dropbox.com

 

[smitpau]

Thanks this seems to be working, deleting the cells in the two rows below did remove the spill error.

The formulas start in H21, I21 and so on across the columns.

Top 3.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R
2		2	3	4	5	6	7
3		01/04/2020	08/04/2020	15/04/2020	22/04/2020	29/04/2020	06/05/2020	13/05/2020	20/05/2020	TOTAL	Average	Count
4	NOTES	Score	Score	Score	Score	Score	Score	Score	Score	TOTAL	Average
5	Paul & Family		42	40	43	41	48.5			214.5	42.90	5
6	Katherine Walker	33.25		39		32.5	39			143.75	35.94	4
7	Elsi	34	36	39						109	36.33	3
8	Alan	31	31.5	36	33	39	37			207.5	34.58	6
9	Katherine & Mark Dawson		32.5	34	31	35.5	40			173	34.60	5
10	John	27		31	29	25	32			144	28.80	5
11	Sian			28						28	28.00	1
12	Imogen and Rob			25	28					53	26.50	2
13	Kate			19						19	19.00	1
14	Luci & Russell				35	31	40			106	35.33	3
15	Jessie & Jean	24	30		43	36	33			166	33.20	5
16	Susanna & Mick		32		28	40				100	33.33	3
17	Bridget & Michael									0	0.00	0
18
19
20	NUMBERS	4	5	7	7	6	5	0	0
21	1ST	Elsi	Paul & Family	Paul & Family	Paul & Family	Paul & Family	Paul & Family			Paul & Family	Paul & Family
22	2ND	Katherine Walker	Elsi	Katherine Walker	Jessie & Jean	Susanna & Mick	Katherine & Mark Dawson			Alan	Elsi
23	3RD	Alan	Katherine & Mark Dawson	Elsi	Luci & Russell	Alan	Luci & Russell			Katherine & Mark Dawson	Katherine Walker
Sheet2

Cell Formulas
Range	Formula
P5:P17	P5	=SUM(H5:O5)
Q5:Q17	Q5	=SUM(IFERROR(P5/R5,0))
R5:R17	R5	=COUNT(H5:M5)
H20:O20	H20	=COUNT(H7:H17)
H21:M23	H21	=INDEX(SORT($G$5:H$17,H$2,-1),SEQUENCE(3))
N21:O21	N21	=IFERROR(OFFSET($G5,MATCH(LARGE(N5:N17,1),N5:N17,0)-1,0),"")
P21:Q21	P21	=IFERROR(OFFSET($G5,MATCH(LARGE(P5:P20,1),P5:P20,0)-1,0),"")
N22:O22	N22	=IFERROR(OFFSET($G5,MATCH(LARGE(N5:N17,2),N5:N17,0)-1,0),"")
P22:Q22	P22	=IFERROR(OFFSET($G5,MATCH(LARGE(P5:P20,2),P5:P20,0)-1,0),"")
N23:O23	N23	=IFERROR(OFFSET($G5,MATCH(LARGE(N5:N17,3),N5:N17,0)-1,0),"")
P23:Q23	P23	=IFERROR(OFFSET($G5,MATCH(LARGE(P5:P20,3),P5:P20,0)-1,0),"")
Dynamic array formulas.

 

[Detectiveclem]

Hi Smitpau,

Thank you for all your hard work on this. Now works perfectly.

Really appreciated.

Have a great day

 

[smitpau]

Glad to help Detectiveclem