ABCDEFGHIJKL
1
NEW DateNEW RankPlayer RankNEW DecayNEW MatchNEW RandStartRankSheetStartDateDaysInRangeRankCapRankFloorDecay%
2
1 Jan 20201,7001,700#REF!#REF!417001/1/202020602000100020.00%
3
2 Jan 2020#REF!#REF!418001/1/202020602000100020.00%
4
3 Jan 2020#REF!1The basic gist of the system is this:

- Each player starts with a rank, assigned on them being entered into a database (cell G2 -> B2)
- Their rank for the next day (B3) is the rank on the day before (B2) plus two other effects (C2,D2)
- One of these effects is a "Decay" change (C2) which softly limits ranks which get too high / low
- One of these effects is a "Match" changes - results from actual matches they play
- These Decay and Match changes have logic that DEPENDS on what their rank was that day

So, more concisely...

- The rank in B2 is the first seeded value
- C2 and D2 are generated USING B2, plus some other logic (heavily simplified here)
- The rank in B3 is generated USING B2, C2, D2 (as their sum)
- Then C3 and D3 are generated USING B3... Etc.

Here is the problem:

We fundamentally want a dynamic solution which will add new rows (dates & ranking info for those dates) as time goes on. For this reason, we chose ARRAYFORMULA. However, we can't get all 3 columns to work in ARRAYFORMULA - at least one appears to need to be regular formulas, or it breaks. (We're not sure if this is true, but we can't find a setup that works!) So for right now, all our logic in B:B is formulas that need to be dragged down.

Additionally, even with this modification, the sheet currently needs iterative calculation turned ON with a quite high number of iterations... and it runs pretty sluggishly even with relatively few dates & changes here. With a larger data set, the sheet becomes almost unusable.

So we are IDEALLY looking for:

* ARRAYFORMULAS in B3, C2, D2, which generate the same output
* Speedy/efficient implementations that won't bog down the sheet
* Implementation that isn't so highly specific that we're able to switch out the actual Decay / Match Change logic that's been simplified heavily for this sheet (i.e. replace the IFs referring to B2:B etc. with more sophisticated IFs, but fundamentally still ones that only depend on B2:B)

If you have other ideas which still produce a dynamic range & are efficient, we're also open to not using ARRAYFORMULA - but that's what we've been using so far all over the sheet.

Additionally, because the "first" dependence is B3 on B2,C2,D2... we've been mostly tinkering with what ARRAYFORMULA / other formula to put in B3:B... but if you can spot any fixes to the ARRAYFORMULAs in C2 or D2, we *can* change those.
5
4 Jan 2020#REF!1
6
5 Jan 2020#REF!1
7
6 Jan 2020#REF!4
8
7 Jan 2020#REF!1
365
29 Dec 2020#REF!2
366
30 Dec 2020#REF!3
367
31 Dec 2020#REF!1
368
1 Jan 2021#REF!4
369
2 Jan 2021#REF!2
370
3 Jan 20214
371
4 Jan 20213
372
5 Jan 20214
373
6 Jan 20211
374
7 Jan 20213
375
8 Jan 20211
376
9 Jan 20213
377
10 Jan 20213
378
11 Jan 20213
379
12 Jan 20213
380
13 Jan 20214