![]() |
#1
|
|||
|
|||
![]()
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 |
#2
|
||||
|
||||
![]()
Show how you would calculate the percentage for say, just Eintracht Frankfurt.
|
#3
|
|||
|
|||
![]()
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. |
#4
|
|||
|
|||
![]()
cross posted at MrExcel
|
#5
|
||||
|
||||
![]()
At MrExcel you said:
Quote:
It looks like they played 31 games, not 34 as there's no score and no date for 3 of them. Quote:
(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 Last edited by p45cal; 10-24-2024 at 09:33 AM. |
#6
|
||||
|
||||
![]()
__________________
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 |
#7
|
|||
|
|||
![]()
Actually, it is the cell W in which these "3+" goals should be considered.
Thank you. |
#8
|
||||
|
||||
![]() |
#9
|
|||
|
|||
![]()
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 ![]() |
#10
|
|||
|
|||
![]()
Hi P45Cal
So, which formulas did you use to show this statistics? Thanks a lot in advance. Vlad |
#11
|
||||
|
||||
![]()
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… |
#12
|
|||
|
|||
![]()
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 |
#13
|
||||
|
||||
![]()
What version of Excel are you using?
|
#14
|
|||
|
|||
![]()
I use Excel 2016 so basically I cannot use XLOOKUP function but only INDEX, MATCH and VLOOKUP function...
|
#15
|
||||
|
||||
![]()
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 |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
count formula to count based on multiple criteria and return unique rows | Sphinxakop | Excel | 0 | 02-01-2023 12:18 PM |
![]() |
Stefan_Deckers | Excel | 3 | 09-19-2020 02:36 PM |
![]() |
wheddingsjr | Excel | 4 | 09-01-2020 09:52 PM |
![]() |
Oleg | Excel | 5 | 02-22-2017 03:05 AM |
![]() |
salmonrose | Word | 1 | 10-15-2011 05:10 AM |