Excel formula help

[eltax91]

Hello gang

Anyone smart with excel? I've got a spreadsheet for the kids football team that I coach and I'm stuck with something.

Sheet 1 - Overall Results/ Stats
Sheet 2 - Player Stats League matches
Sheet 3 - Player Stats Cup matches
Sheet 4 - Player Stats Friendly matches

Each sheet has a row for each game. with all the 13 players in columns, their minutes played and goals scored underneath their name (so 26 columns). Row 1 has the two cells merged for each column (B/C, D/E etc etc). At the bottom (row 18 currently) there's a sum of their minutes and goals so i can track these. Looks like this:



In sheet 1, i have a formula that goes to sheet 2 Indexes row 1 (strings of names) does MATCHMAX on row 18 (highest total goals) and returns the name back from row 1. This is for lead goalscorer in the league.



Is there then a way to also (in the same cell or another one) return the result of the MAX? So for example, instead of just returning the string "Jack" it also returns the value "12" so i know at a glance how many goals jack has scored?

Hope this makes sense! The spreadsheet is also in google sheets if that makes a difference to formulas etc....

[foreright]

Unless I'm missing something, if you have the number of goals per player stored on a single row (and nothing else on that row...) then can you not simply use a formula such as:

=MAX(Sheet2!18:18)

... to get the highest value in that row? You can append it to the existing cell or store it in a separate one or whatever.

[eltax91]

foreright said:

Unless I'm missing something, if you have the number of goals per player stored on a single row (and nothing else on that row...) then can you not simply use a formula such as:

=MAX(Sheet2!18:18)

... to get the highest value in that row? You can append it to the existing cell or store it in a separate one or whatever.

Bloody hell that was too easy!

Edited by eltax91 on Monday 14th November 12:35

[eltax91]

If anyone's still reading, next challenge!

On Sheet 1 I have 3 separate sets of values (as per the second picture). Totals for League, cup and friendlies.

I then have a "Season Overall" which adds up all those values, but i've never been able to work out Lead Goalscorer again overall! I'm using INDEX to work out the top GS's for each, E.G:-

=INDEX('Player Stats - League'!B1:AA1,MATCH(MAX('Player Stats - League'!B18:AA18),'Player Stats - League'!C18:AA18,0))

=INDEX('Player Stats - Cup'!B1:AA1,MATCH(MAX('Player Stats - Cup'!B18:AA18),'Player Stats - Cup'!C18:AA18,0))

=INDEX('Player Stats - Friendlies'!B1:AA1,MATCH(MAX('Player Stats - Friendlies'!B18:AA18),'Player Stats - Friendlies'!C18:AA18,0))

Is there a way in a single cell to combine all 3 of those into a season overall top GS?

Cheers

[beambeam1]

I would do a separate table with SUMIF for each tournament and player, add a further column for totals across each tournament value then RANK.

SUMIF

RANK

If you want a better representation (imo) of goal scoring performance then perhaps adjust the numbers Per 90 Mins Played. Of course, if your kids aren’t playing full length matches then multiple by minutes of a normal match for them instead.

It’s a nicer way of representing player impact on the pitch but obviously defenders and goalkeepers aren’t going to score as much as centre forwards and midfielders so context is key here. There are ways of quantifying total contribution but involves performance analysis and literally counting every tackle, kick and shot on goal to name but a few variables!

[foreright]

I would say also that there’s a non-zero chance of more than one player being the highest goal scorers too which complicates things somewhat!

[Arranguez]

Probably easy but Google just gives me summing a number of cells for this. So help most appreciated please.

I’d like a formula to use in Excel to look for a value in a cell (Y) and if there to multiply two other cells together to give a value.

I thought I could use IF or SUMIFS but they don’t seem to work with calculations. Unless I’m misreading the description.

[CoolHands]

=IF(y=>0,a2*a3,””)

That symbol should be greater than >

I think that will work?

[Arranguez]

I can’t get that to work but thank you for giving it a go.

Essentially:

Cell E3 contains a Y or an N. If it contains Y then sum together N3*C3.

If only I used ChatGPT!

[Arranguez]

Well I signed up to ChatGPT.

=IF(E3=“Y”,N3*D3,0)

Worked a treat. Now I need to nest a function so if it has the letter N it returns a different sum.

[Doofus]

Arranguez said:

Well I signed up to ChatGPT.

=IF(E3=“Y”,N3*D3,0)

Worked a treat. Now I need to nest a function so if it has the letter N it returns a different sum.

That's called an IF THEN ELSE, and says "IF E3 is 'Y', THEN N3xD3, ELSE do nothing", so what you need to do is replace the zero (the 'do nothing') with whatever you want to happen when E3 is NOT 'Y'.

[Arranguez]

That is also useful, thank you. I’ve simplified the formula based on that.

[Arkose]

I need a formula that OP used I think for his highest goalscorer ?

I have a range of scores, then I need it to look across to a column for the corresponding "team name" ?

(wish I was better at excel formula!)

EDIT :

TEAM 1 | 2 (PTS)
TEAM 2 | 5 (PTS)
TEAM 3 | 4 (PTS)

Winners | TEAM 2

I need to to find max value in the points column to return TEAM 2 in the winners cell ...

Edited by Arkose on Tuesday 18th June 10:59

[Doofus]

Arkose said:

I need a formula that OP used I think for his highest goalscorer ?

I have a range of scores, then I need it to look across to a column for the corresponding "team name" ?

(wish I was better at excel formula!)

Before getting better ar Excel, you need to get better at explaining what it is that you want.

[mmm-five]

Arkose said:

I need a formula that OP used I think for his highest goalscorer ?

I have a range of scores, then I need it to look across to a column for the corresponding "team name" ?

(wish I was better at excel formula!)

EDIT :

TEAM 1 | 2 (PTS)
TEAM 2 | 5 (PTS)
TEAM 3 | 4 (PTS)

Winners | TEAM 2

I need to to find max value in the points column to return TEAM 2 in the winners cell ...

Edited by Arkose on Tuesday 18th June 10:59