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)