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

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi, just tried what you suggest and get the following error message #SPILL!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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.
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Hopefully this will work

 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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
GHIJKLMNOPQR
2234567
301/04/202008/04/202015/04/202022/04/202029/04/202006/05/202013/05/202020/05/2020TOTALAverageCount
4NOTESScore Score Score Score Score Score Score Score TOTALAverage
5Paul & Family4240434148.5214.542.905
6Katherine Walker 33.253932.539143.7535.944
7Elsi34363910936.333
8Alan3131.536333937207.534.586
9Katherine & Mark Dawson32.5343135.54017334.605
10John273129253214428.805
11Sian 282828.001
12Imogen and Rob 25285326.502
13Kate191919.001
14Luci & Russell35314010635.333
15Jessie & Jean243043363316633.205
16Susanna & Mick32284010033.333
17Bridget & Michael00.000
18
19
20NUMBERS45776500
211STElsiPaul & FamilyPaul & FamilyPaul & FamilyPaul & FamilyPaul & Family  Paul & FamilyPaul & Family
222NDKatherine Walker ElsiKatherine Walker Jessie & JeanSusanna & MickKatherine & Mark Dawson  AlanElsi
233RDAlanKatherine & Mark DawsonElsiLuci & RussellAlanLuci & Russell  Katherine & Mark DawsonKatherine Walker
Sheet2
Cell Formulas
RangeFormula
P5:P17P5=SUM(H5:O5)
Q5:Q17Q5=SUM(IFERROR(P5/R5,0))
R5:R17R5=COUNT(H5:M5)
H20:O20H20=COUNT(H7:H17)
H21:M23H21=INDEX(SORT($G$5:H$17,H$2,-1),SEQUENCE(3))
N21:O21N21=IFERROR(OFFSET($G5,MATCH(LARGE(N5:N17,1),N5:N17,0)-1,0),"")
P21:Q21P21=IFERROR(OFFSET($G5,MATCH(LARGE(P5:P20,1),P5:P20,0)-1,0),"")
N22:O22N22=IFERROR(OFFSET($G5,MATCH(LARGE(N5:N17,2),N5:N17,0)-1,0),"")
P22:Q22P22=IFERROR(OFFSET($G5,MATCH(LARGE(P5:P20,2),P5:P20,0)-1,0),"")
N23:O23N23=IFERROR(OFFSET($G5,MATCH(LARGE(N5:N17,3),N5:N17,0)-1,0),"")
P23:Q23P23=IFERROR(OFFSET($G5,MATCH(LARGE(P5:P20,3),P5:P20,0)-1,0),"")
Dynamic array formulas.
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi Smitpau,

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

Really appreciated.

Have a great day
 

Watch MrExcel Video

Forum statistics

Threads
1,123,239
Messages
5,600,486
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top