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

VKiprijan · #17 10-29-2024, 11:27 AM

Quote:

Originally Posted by p45cal

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:

Code:

=TeamsWithMostPercentGames($B$5:$H$346,AP29)

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:

Code:

=TeamsWithMostPercentGames($B$5:$H$346,5,7)

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.

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.

Similar Threads
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
How can I count per year and per week the total number of times a code occurs	Stefan_Deckers	Excel	3	09-19-2020 02:36 PM
Why does the total row of my pivot table not tie to the real count using distinct count	wheddingsjr	Excel	4	09-01-2020 09:52 PM
Formula for calculating total when number and letter in one cell	Oleg	Excel	5	02-22-2017 03:05 AM
Word: count total page of each section	salmonrose	Word	1	10-15-2011 05:10 AM