No apologies needed!...I understand your perspective. The formula is focused on the main challenge of eliminating the long helper columns that involve sequential row-by-row calculations. It does that and more, but whether that's necessary now is debatable. Since you've hardwired the results of those helper calculations, and workbook performance is now acceptable, the approach I've mapped out may very well be a solution in search of a different problem. In order to make the formula easier to maintain (difficult to say with a straight face), I've attempted to generalize it so that the same formula can be applied without modification to the first three subsections of the summary table (for W, L, and D). To do that, of course, the formula needs some inputs that were previously hardwired into different variations of your original formula. These inputs need to be set up in some local helper cells (the orange cells in my earlier example) around the summary table. Once that is done, assuming the source data column mappings are correct, you should get results that (hopefully) make sense. I have an idea for making modest extensions to the formula so that it would also work on the Goals Scored and Goals Conceded subsections.
But a lot happens inside the formula that will require some study time to see how the formula works. Are you familiar with how to view an intermediate result inside a LET function? If not, I would recommend setting up a small example---one that can be readily examined by eye to confirm that intermediate results make sense. The example below would be a good starting point. I've slightly altered the layout from the example in post #28 to show dates in two formats (because internal to the formula, all dates are numerical, not in dd/mm/yyyy format), so this extra numerical date column facilitates comparisons with results from the formula. And the four input cells are shifted so that the "outcome" appears in the same column as the formula, and the other three inputs appear on a row to the left (although not all three on the same row to keep the example from sprawling across the screen too far). You should be able to copy the example directly from this post (using the small clipboard icon in the upper left of the minisheet), and paste it into cell B1 of an empty worksheet (the same cell shown in the upper left of the posted minisheet). That should duplicate what is shown, other than setting up data validation lists for the four input (blue) cells...or you can simply type the desired query inputs in those cells. The single formula has been edited slightly, by giving the final output formula a variable name, and then after the final formula, we can type the name of any variable that should be displayed by the formula. I've called the formula that generates the final results, the three-cell spilling array, "finres"...so the last variable in the formula would be finres to see the final result. But if you want to see the "rary" array or the filtered data "fd" array, simply replace the last formula argument with the appropriate variable name. The example below shows the revised filtered array "revfd" that I described earlier. It offers a clear visualization of the streaks present. I recommend using the detailed description in post #28 and step through each of the variables in the formula to visualize the operations. Then try different inputs and alter the data table to create various types of streaks to confirm the performance.
Feel free to post back with any questions, suggestions, or reports of incorrect results.
| MrExcel_20240321 (version 1).xlsx |
|---|
|
|---|
| B | | | E | | | | | J | K | L | M | N | O | P | Q | R | S | T | U |
|---|
| 1 | | | | | | | | | | | | | | | | outcomes --> | | W | | |
|---|
| 2 | Date | | | Location | | | | | Match Type | Result | Goals Scored | Goals Conceded | Numerical Date | | | | | TOTAL | TIMES | DATES |
|---|
| 3 | 01/02/2024 | | | A | | | | | League | W | 3 | 2 | 45323 | | match location | All | | 45323 | W | |
|---|
| 4 | 02/02/2024 | | | H | | | | | League | L | 1 | 2 | 45324 | | match type | All | | | | | | |
|---|
| 5 | 03/02/2024 | | | H | | | | | League | L | 0 | 2 | 45325 | | calc type | woOpp | | | | | | |
|---|
| 6 | 04/02/2024 | | | H | | | | | FA Cup | D | 3 | 3 | 45326 | | | | | 45326 | D | |
|---|
| 7 | 05/02/2024 | | | A | | | | | League | L | 1 | 3 | 45327 | | | | | | | | | |
|---|
| 8 | 06/02/2024 | | | H | | | | | League | W | 4 | 2 | 45328 | | | | | 45328 | W | |
|---|
| 9 | 07/02/2024 | | | A | | | | | League | W | 1 | 3 | 45329 | | | | | 45329 | W | |
|---|
| 10 | 08/02/2024 | | | H | | | | | League | W | 0 | 1 | 45330 | | | | | 45330 | W | |
|---|
| 11 | 09/02/2024 | | | H | | | | | League | L | 1 | 3 | 45331 | | | | | | | | | |
|---|
| 12 | 10/02/2024 | | | H | | | | | FA Cup | W | 5 | 2 | 45332 | | | | | 45332 | W | |
|---|
| 13 | 11/02/2024 | | | A | | | | | FA Cup | L | 2 | 3 | 45333 | | | | | | | | | |
|---|
| 14 | 12/02/2024 | | | A | | | | | League | W | 3 | 2 | 45334 | | | | | 45334 | W | |
|---|
| 15 | 13/02/2024 | | | H | | | | | League | W | 2 | 2 | 45335 | | | | | 45335 | W | |
|---|
| 16 | 14/02/2024 | | | A | | | | | League | D | 1 | 1 | 45336 | | | | | 45336 | D | |
|---|
| 17 | 15/02/2024 | | | H | | | | | League | W | 4 | 1 | 45337 | | | | | 45337 | W | |
|---|
| 18 | | | | | | | | | | | | | | | | | | | | |
|---|
|
|---|
