Hello to All,



I have a spreadsheet of soccer matches in which I would like to build a table to be as a top list of soccer teams with the highest percentage of goals that those teams had yielded over the number of games they played so far.
The number of games should be automatically calculated after each round is played.

Any idea of which formulas I should use in order to get this done?


Thanks everyone in advance.

Vlad

Attached Files

Soccer League v2.xlsx (138.4 KB, 10 views)

Show how you would calculate the percentage for say, just Eintracht Frankfurt.

Hi,

Yes, we can take Eintracht Frankfurt, for example, which plays home and away games and sometimes in those games there are at least 3 or more goals (3+) scored by both teams.
If there are 10 games played so far, I would like to calculate how many times there were "3+" goals in those games which Eintracht Frankfurt played. So, if for example Eintracht Frankurt played 5 games with 3+ goals out of 10 games that percentage will obviously be 50%.

So, how do we create a formula that will show the list of the teams (cells AP29-AP39) that score the most 3+ goals (percentage in the cells AR29-AR39)?


Thanks.

cross posted at MrExcel

At MrExcel you said:Here's a screenshot of all Dortmund's games.
It looks like they played 31 games, not 34 as there's no score and no date for 3 of them.
I count only 15 (marked in column I with an x). This about 48% (15/31).
(And only 2 games where both teams had 3+ goals)

Can you confirm that I should only be looking at columns F & G because they are the full time scores?

2024-10-23_172940.jpg

Please add any other links to wherever else you've posted this question to.
Formula to count % of goals in a total number of games | MrExcel Message Board


2024-10-24_173155.jpg

Link to Xpost Formula to count % of goals in a total number of games | MrExcel Message Board

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Actually, it is the cell W in which these "3+" goals should be considered.

Thank you.

Could you check a few of these and tell me if they are correct?:


2024-10-25_104114.jpg

Hi,

Yes, I can confirm that all those percentages are correct. That's exactly how these statistics should look alike, not only one team but also other teams that have the highest percentage.

This is great job, congratulations

Hi P45Cal

So, which formulas did you use to show this statistics?


Thanks a lot in advance.
Vlad

Well, that picture wasn't the result of a formula, instead I used Power Query.
In the attached, on sheet Bundes League (2), at cell L4, there's a result table which you can update by right-clicking in it and choosing Refresh. It's based on two proper Excel tables: (a) one called Table1 at B4:H346 and (b) the other called Table4 at J4:J15, both on that same sheet.

Since then, I developed a deceptively simple named lambda function TeamsWithMostPercentGames.

You can see it in action at cell AS28 of the Bundes League sheet. It needs to be copied down.

2024-10-26_162447.jpg

You can see that it uses only the raw data.
If you want to copy the lambda function to another workbook, copy that single sheet to your workbook, then immediately delete it from yor workbook. The function should remain. You can see it in Name Manager.

The rest below is just for information and the relevant formulae can be deleted/ignored:
In cell AW28 is the development version of TeamsWithMostPercentGames, and the formula in cell BC28 is the forerunner to that.


Now I'll just wait for the questions…

Attached Files

msofficeforums52938Soccer League v2-1.xlsx (175.0 KB, 5 views)

Well, this approach is great and your expertise in this topic is quite impressive but also very complicated for me as I don't have experience with PowerQuery.
Is there any embedded formula with INDEX, MATCH, VLOOKUP and COUNT that I could use to get the same result as you did?

Thank you so much.
Vlad

What version of Excel are you using?

I use Excel 2016 so basically I cannot use XLOOKUP function but only INDEX, MATCH and VLOOKUP function...

Also cross posted at Formula to identify which teams and their percentage with the highest % of goals scored | Excelguru Forums
and
Formula to identify which teams and their percentage with the highest % of goals scored

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post