I'm trying to figure out what would be the best formula to write for the "Shortfall" section
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
This is the condition i'm trying to meet
1) If the "shortfall" is positive, I want to show 0, but I want that positive number to be applied to the next period when we have negative shortfall
So for example, 1/1/2020, we have positive number 7,659. I still want to show 0 for the period, but this positive number to be applied on 7/1/2021. So instead of 79,701, I want number to be "54,660"
Please advise and thanks for the inputs
Date | Period | Revenue | Expense | Shortfall |
7/1/2018 | 3 | 0 | (82,242) | (82,242) |
10/1/2018 | 4 | 0 | 0 | 0 |
1/1/2019 | 5 | 82,196 | (82,242) | (47) |
4/1/2019 | 6 | 0 | 0 | 0 |
7/1/2019 | 7 | 0 | (81,745) | (81,745) |
10/1/2019 | 8 | 0 | 0 | 0 |
1/1/2020 | 9 | 89,404 | (81,745) | 7,659 |
4/1/2020 | 10 | 0 | 0 | 0 |
7/1/2020 | 11 | 0 | (81,002) | (81,002) |
10/1/2020 | 12 | 0 | 0 | 0 |
1/1/2021 | 13 | 106,043 | (81,002) | 25,041 |
4/1/2021 | 14 | 0 | 0 | 0 |
7/1/2021 | 15 | 0 | (79,701) | (79,701) |
10/1/2021 | 16 | 0 | 0 | 0 |
1/1/2022 | 17 | 112,140 | (1,324,701) | (1,212,562) |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
This is the condition i'm trying to meet
1) If the "shortfall" is positive, I want to show 0, but I want that positive number to be applied to the next period when we have negative shortfall
So for example, 1/1/2020, we have positive number 7,659. I still want to show 0 for the period, but this positive number to be applied on 7/1/2021. So instead of 79,701, I want number to be "54,660"
Please advise and thanks for the inputs