#### lk200

##### New Member
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"

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Gerald Higgins

##### Well-known Member
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

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

Code:
``=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.

Last edited:

#### lk200

##### New Member
You are absolutely right

My apology

#### frabulator

##### Board Regular
You are absolutely right

My apology

No problem ! Glad I could help.

Replies
3
Views
760
Replies
3
Views
505
Replies
5
Views
432
Replies
5
Views
2K
Replies
1
Views
750

1,195,654
Messages
6,010,935
Members
441,575
Latest member
JOHNNY18031

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back