Hello, I am currently trying to find a way to average the last three values in a column. Currently, my Data Set looks like this:
| Rank | Division | Fiscal Year | Participant Count |
| 1 | A | 2020 | 14 |
| 2 | A | 2021 | 13 |
| 3 | A | 2022 | 12 |
| 4 | A | 2023 | |
| 5 | A | 2024 | |
| 6 | A | 2025 | |
| 7 | A | 2026 | |
| 8 | A | 2027 | |
| 9 | A | 2028 | |
| 10 | A | 2029 | |
| 11 | A | 2030 | |
| 12 | B | 2020 | 207 |
| 13 | B | 2021 | 196 |
| 14 | B | 2022 | 213 |
| 15 | B | 2023 | |
| 16 | B | 2024 | |
| 17 | B | 2025 | |
| 18 | B | 2026 | |
| 19 | B | 2027 | |
| 20 | B | 2028 | |
| 21 | B | 2029 | |
| 22 | B | 2030 | |
My desired Output looks like this:
| Rank | Division | Fiscal Year | Participant Count | Projected |
| 1 | A | 2020 | 14 | 14 |
| 2 | A | 2021 | 13 | 13 |
| 3 | A | 2022 | 12 | 12 |
| 4 | A | 2023 | | 13 |
| 5 | A | 2024 | | 12.66667 |
| 6 | A | 2025 | | 12.55556 |
| 7 | A | 2026 | | 12.74074 |
| 8 | A | 2027 | | 12.65432 |
| 9 | A | 2028 | | 12.65021 |
| 10 | A | 2029 | | 12.68176 |
| 11 | A | 2030 | | 12.66209 |
| 12 | B | 2020 | 207 | 207 |
| 13 | B | 2021 | 196 | 196 |
| 14 | B | 2022 | 213 | 213 |
| 15 | B | 2023 | | 205.3333 |
| 16 | B | 2024 | | 204.7778 |
| 17 | B | 2025 | | 207.7037 |
| 18 | B | 2026 | | 205.9383 |
| 19 | B | 2027 | | 206.1399 |
| 20 | B | 2028 | | 206.594 |
| 21 | B | 2029 | | 206.2241 |
| 22 | B | 2030 | | 206.3193 |
If Participant Count is not blank, then it will just return that value. However, if it is blank, then it takes the average of the last 3 values before it.
Thank you for any help or ideas!
