Formula to count % of goals in a total number of games

[VKiprijan]

Hello to All,

I have a spreadsheet of soccer matches in which I would like to build a table (columns AO thru AR) to look like 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

 

[Peter_SSs]

Glad that you got XL2BB going.

Does post #9 cover these points?

..

• Manually filled in the expected results for some sample data
• ...
• Explain, in relation to the specific sample data, how you got those expected results manually.

 

[VKiprijan]

Glad that you got XL2BB going.

Does post #9 cover these points?

Yes it does. That is the table from which I need to calculate the number of goals (column W3) in a separate table with the team and percentage.

Thanks.

 

[Peter_SSs]

Yes it does.

I don't understand.

If column W contains the expected results then the formulas you have in that column must be the correct ones.
If column W does not contain the results you want, then where are the manually filled in expected results?

The final bolded point in my last post (& post 2) was asking for an explanation of how the expected results were obtained manually. There is no explanation of anything in post 9

 

[VKiprijan]

Hi Peter,

Sorry for the confusion, I meant to say that column W is one of the sources (amongst the columns in the range from P thru Z) that I need to use to produce the statistical percentage of goals for certain teams. For that statistics, there is a smaller table (please see below) that needs to be populated with formulas that will produce the teams and their percentages for the specific number of goals scored.

Soccer League v2.xlsx
	AO	AP	AQ	AR
28	Goals	Team who has yielded the most % of games		Percentage
29	0 - 1
30	0 - 2
31	2 - 3
32	2 - 4
33	3 - 5
34	4 - 6
35	2+
36	3+
37	4+
38	5+
39	7+
Bundes League

The formula that shows the team should be populated in the AP29-39 columns.

I hope that this clarifies but if not please let me know.

Thanks a lot.
Vlad

 

[Peter_SSs]

Sorry, I have asked for certain things three times and you seem unprepared to provide them so I'm afraid that I will have to move on to other questions.

 

[VKiprijan]

Hello Peter,

Sorry if it was confusing again. Let me explain it in more detail.
I want to make a statistic table that will show which soccer teams have the highest prevalence in goals scored when they played against other teams over the whole season. The source of those goals is in the columns P thru Z and the table that will show these soccer teams and their percentage is in AO28:AR39.
Just to give you an example, let's compare which soccer teams had the most frequent number of goals 0-1. If we do the manual count of 0-1 occurrence in column P we can determine that there were four teams with the highest prevalence of 0-1 goals in 8 out of 31 games played (25.81%). That same calculation can be done for other criteria such as 2+, 3+ or more goals per game.
What I need is actually a formula that can count this percentage for me. I am using Excel 2016 so unfortunately, I can't use XLOOKUP function. So, I guess the formula should contain formulas such as INDEX, MATCH, VLOOKUP or even some statistical formula.

Thanks.

 

[arthurbr]

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

Formula to identify which teams and their percentage with the highest % of goals scored

Hi to all Excel experts, I want to make a statistic table that will show which soccer teams have the highest prevalence in goals scored when they played against other teams over the whole season. The source of those goals is in the columns P thru Z and the table that will show these soccer...

www.excelforum.com

 

[Fluff]

I apologize as I wasn't aware of it.

But you obviously have not read the rules, also you have not done what I asked you to do, namely

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

Please provide links to ALL sites where you have asked this question.

 

[VKiprijan]

Hello,

Here are the links to other forums where I have reposted this topic:

Formula to count % of goals in a total number of games - Page 2

Formula to count % of goals in a total number of games

Formula to identify which teams and their percentage with the highest % of goals scored

Formula to identify which teams and their percentage with the highest % of goals scored - OzGrid Free Excel/VBA Help Forum

Formula to identify which teams and their percentage with the highest % of goals scored

Formula to identify which teams and their percentage with the highest % of goals scored

Hi to all Excel experts, I want to make a statistic table that will show which soccer teams have the highest prevalence in goals scored when they played against other teams over the whole season. The source of those goals is in the columns P thru Z and the table that will show these soccer...

www.excelforum.com

I apologize for this again.

Thanks.

 

[Peter_SSs]

Thanks for posting the cross-post links.
I'm wondering if you followed the advice given earlier ..

Be sure to follow & read the link at the end of the rule too!

.. particularly the section "So What's the Big Deal?"

I, for one, would not be checking four other forums to see if you already had an answer there before investing time trying to produce one myself.