Find address of max value, sports stats

Rikib1999

New Member
Joined
Aug 20, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Hi,
I have a table of sport team, which shows, how many goals (with assissts) 2 players scored together (so if player 5 scored a goal and player 16 assisted on this goal they will have one common goal).
There is an example of the table, players numbers are in the first column and row, common goals are for the player from the column and row:
571623
5302
720
161
23

Now, I need in another 3 cells, the amount of most goals of a pair, so the max value of the table (can be done with MAX formula), then the numbers of the 2 players which have that most common goals.
I tried to find them out by INDEX, MATCH, MAX, ADDRESS, CELL... but nothing worked.
Do somebody know, how should I approach this problem?
Thanks!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Such tasks are never as simple as you would like them to be, I think that the method I've used below eliminates all possible errors in the results. I've deliberately set a tie for the max to further test the formulas. Tested with a 3 way tie with no apparent issues.
Book1
ABCDEFGHI
1571623MAXFirst PlayerSecond Player
25302357
3730716
4161  
523
Sheet9
Cell Formulas
RangeFormula
G2G2=MAX($B$2:$E$5)
H2:H4H2=IFERROR(INDEX($A$1:$A$5,AGGREGATE(15,6,ROW($B$2:$E$5)/($B$2:$E$5=$G$2),ROWS(H$2:H2))),"")
I2:I4I2=IFERROR(INDEX($A$1:$E$1,AGGREGATE(15,6,COLUMN($B$2:$E$5)/(INDEX($B$2:$E$5,MATCH($H2,$A$2:$A$5,0),0)=$G$2),COUNTIF(H$2:H2,H2))),"")
 
Upvote 0

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

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
Back
Top