Numbers, last 5 games football results

That is an array formula. The IF inside the LARGE function is making it act like "LARGEIF". There is no LARGEIF function so we have to improvise.

Here is a shot at it. It took two extra columns and it assumes the data is in chronological order. Hopefully it won't be too slow for 12,000 rows and trying to get the results for numerous teams. Numbers is not the speed demon Excel is when it comes to calculations. Excel is astonishingly fast. Numbers is not, though it has gotten better over the years. It works better for smaller datasets. You may find it too slow and may want to use Excel.

K2 is the team name.

K3 is the number of games you want the stats for (5, 10, 15, etc)

H2 =COUNTIF(B2:C$22,B2)

I2 =COUNTIF(B2:C$22,C2)

You will want to increase those ranges to whatever your last row is (vs row 22 in my example).

Copy/Paste to the rest of the cells in the columns.

These two columns are counting the number of games played by the teams on that row, counting down.

K4 ="Wins = "&COUNTIFS(H,"<="&K3,B,K2,F,"H")+COUNTIFS(I,"<="&K3,C,K2,F,"A")

K5 ="Losses = "&COUNTIFS(H,"<="&K3,B,K2,F,"A")+COUNTIFS(I,"<="&K3,C,K2,F,"H")

K6 ="Draws = "&COUNTIFS(H,"<="&K3,B,K2,F,"D")+COUNTIFS(I,"<="&K3,C,K2,F,"D")

You can adapt the formulas to your summary table.

You might want to try it in the data table first (i.e., using column K) and see how fast or slow it is. You can copy/paste cells K2:K6 repeatedly to other rows in column K to see how many it can do before it slows down.

More information needed. A screenshot of your table might be helpful. The Excel formula that worked in that table would also be handy to see. I suspect it was an Excel "array" formula because most of the "normal" formulas are a direct 1:1 import. It might be possible to replicate it or design a formula that does the same thing a different way or it might require a new column to do some calculating that an "array" formula does within the formula.

That would have taken a few seconds in Excel. I don't know how they do it so fast or why Numbers is many orders of magnitude slower. I have been trying to do a test right to see the speed difference for this particular problem but Numbers keeps crashing. I've been unable to extend it to 12,000 rows without it taking minutes "calculating" then crashing when I try to make changes.

In Excel I was able to extend the data to 12,000 rows. I edited the COUNTIF formulas in Row 2 to include all 12,000 rows then copy/pasted them all the way to row 12,000. It took 2 seconds to complete. Changing the team name in K2 provides instantaneous results.

In Numbers, I extended the table to 12,000 rows before editing the COUNTIF formulas so it filled down with the old formulas, generating error messages for all the invalid references. I am unable to delete them without it crashing the app. I tried deleting a few hundred at a time then a few thousand at a time, saving the document after each time, but after a few times it stopped letting me save my changes. In other words, it has failed and is going to crash. I've been at this an hour. I give up.

VERDICT: Use Excel or one of the Excel clones. Maybe Google Sheets can do it. Numbers is not up to the task. I enjoyed the challenge of converting the formula but Numbers cannot do 12,000 rows of this. It is highly disappointing when Excel can do it in 2 seconds.