Excel Formula Advise

[lk200]

I'm trying to figure out what would be the best formula to write for the "Shortfall" section

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

 

[Gerald Higgins]

Just to be clear, why is the shortfall applied on 7/1/2021 ?
Why not on 7/1/2020 ?
Or, for that matter, 4/1/2020 ?

Also, are your dates DD/MM/YYYY or MM/DD/YYYY ?

 

[frabulator]

is this the formula you are looking for? you will need to replace the cells with the ones you are using

=IF(C1=D1,0,IF(C1>D1,C1-D1,D1-C1))

---EDIT---
It is a 'difference' code that will generate the difference between two values. In this case, C1 and D1.

 

[lk200]

You are absolutely right

My apology

 

[frabulator]

You are absolutely right

My apology

No problem ! Glad I could help.