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.