I would not like to create an equivalent formula for Excel 2016. I'll leave that to someone else. However, in the attached I've created a UDF (user-defined function).
This requires macros to be enabled when the workbook is opened.

In cell AQ29 there is the formula:
The range $B$5:$H$346 is your raw data including the column of dates. It doesn't matter if this range contains rows with incomplete data.
The AP29 bit refers to a cell containing the likes of 2-4 and 3+. The function tries to evaluate these values to convert them to 2 values, eg. 2-4 converts to 2 and 4, 3+ converts to 3 and 999.


If the function fails to convert what's in AP29 to numbers you'll get a #VALUE! result. In that case there's a fall back where you can supply the numbers in the formula itself:
With both forms of the formula in Excel 2016 you need to commit (enter) the formula to the sheet in a special way (called array-entering or CSE entering):
First select 2 cells next to each other, eg AQ29:AR29, then type the formula, then rather than just pressing Enter to enter the formula, you need to press Ctrl+Shift+Enter (you should see curly brackets around the whole formula). After that you can copy/drag down both cells at once.
With more recent versions of Excel which spill, there's no need to array-enter these formulae (although it doesn't matter if you do!) and you only need enter the formula in a single cell.

You can see the second form of the formula in use in column AT.

Finally, if you get errors with the formula as I've left them when you recalculate the sheet, I'll need to make a little tweak to the code.

Attached Files

msofficeforums52938Soccer League v2-1.xlsm (149.3 KB, 4 views)

According to XLForum, this OP has posted the same question on 5 other forums .

__________________
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

Hi and I apologize for the late reply.

So, if I understood correctly, I need to create a macro or VB code with this name "TeamsWithMostPercentGames" in which I can copy and paste your code.

However, from the code itself, I cannot see where I need to insert in it the criteria for the table I want to be shown. For example, if I want to remove, add or change criteria for this table (ie. I want to show statistics for the teams that have played GG3+ (both teams need to score a goal and there should be at least 3 or more goals per game) or 1GG (both teams scored a goal in the first half of the game), how I can do that? Also, is there any simplier way to create this kind of statistical table?

Thanks in advance.

Just copy paste the code in Module1 in the workbook I attached (You could even copy the whole module to your workbook).


The udf is for giving you the statistics of numbers of goals scored by both teams in games.
What you're wanting with GG3+ and 1GG (and more?) is quite different, and would need another function/s to be written.
Writing on-sheet formulae using only the built-in sheet functions is made difficult by your arrangement of raw data; each row represents a game, but there are 2 teams on each row and you want statistics on a per team basis. When I explored doing this in Excel 2016 (including using your helper columns O:AN) it just got far too convoluted and I gave up; such a formula would be incomprehensible and difficult to maintain/manage anyway. It still isn't straightforward in more recent versions of Excel.

A simpler way to create a statistical table? Well my first offering using Power Query is a more robust way of extracting, transforming and presenting the data both in a plain table and possibly pivot tables but would require significant learning from you that I fear you wouldn't call it 'simpler'.

You want quite a range of bespoke statistics, so be aware that it takes some significant time for someone to understand them, let alone develop a solution. Is there anywhere on the web where these statistics are explained and used? It would help.

The problem with copying/pasting this module is that it applies to only one tab and I have over 30 other tabs (for Premier League, Spanish, Italian, French,...) in the same file so I don't know how it will behave then. That's why I was wondering if we could use combined O5:AN346 range instead of only B5:H346 as the first shows different goal categories.

I am now aware of any website that describes these types of soccer statistics, you can only find live odds for these on different betting websites. I understand that this is not an easy task but the logic is fairly simple: to find the teams with the most frequent occurrence goal patterns.

It's a function, it will work on all sheets (tabs) in a workbook.

But will it show the results for other teams in different tabs or the function has to be adjusted for each tab?

As long as the data in each tab is laid out in a similar way, that is with 7 columns next to each other:


2024-10-31_100509.jpg


then as long as you have a column somewhere with the goal ranges, like:


2024-10-31_100728.jpg and you adjust the ranges referred to in the function to those cells, there should be no problem.


Note that the function doesn't use the first half results at all (columns 3 and 4 of the referred-to source data), so they could be blank.

Yes, the data is presented in the same way on each of the tabs and first half results should not be used. It's only the range that I will need to adjust.

Thanks a lot.

So scrap this all. Instead create a table with columns like:
League, Season, Game, Team, Oponent, [optionally TeamGoals1stHalf], [optionally TeamGoals2ndHalf], [optionally TeamGoalsOvertime], TeamGoals, [optionally OpponentGoals1stHalf], [optionally OpponenGoals2ndHalf], [optionally OpponentGoalsOvertime], OpponentGoals, ...
For every game, there will be 2 rows of data - a row for 1st team, and a row for 2nd team.
In case you'll enter half-time and offtime results, the columns TeamGoals and OpponentGoals summarize matching team/opponent half-time and offtime results.
In case you enter/calculate team goals as positive numbers, and opponent goals as negative numbers, the match result for team will characterized as SUM(TeamGoals,OpponentGoals). I.e. when the team won, the result is positive, when team lost, the result is negative, and when draw, then the result is 0.

With all game data in this table, you can easily create any number of report sheets. E.g.:
Yearly League report, where use can select (using Data Validation Lists) the league and season, and the result table like you currently have is displayed (probably you need a count of hidden calculated columns in game data table, which are needed so report table fields may be calculated);
A report you asked for at start of this thread (again selecting the league and season);
Etc.