Multi Row Formula help

[agrawaluk]

This is probably way too easy, but I am not able to figure out the logical approach to this. 

I have two Columns: Date and Net Cashflows

I have to create two other columns: Opening Balance and Closing Balance

Opening Balance+Net Cashflow = Closing Balance

Opening balance and closing balance for minimum date should be 0 (this is something that needs to be implemented in the workflow)

Every opening balance = t-1 day's closing balance. 

Sample:

Date	Opening Balance	Net Cashflow	Closing Balance
3/16/2020	0	0	0
3/17/2020	0	8	8
3/18/2020	8	7	15
3/19/2020	15	6	21

[TomWelgemoed · Accepted solution]

Hi @agrawaluk ,

Think this does what you're after. I've assumed you don't have the actual opening & closing balance and have only used the net cashflow. Workflow attached.

Regards,

Tom

[agrawaluk]

Thank you, this works as expected!

[AbhilashR]

@agrawaluk - Thanks for posting this question! I really enjoyed solving this challenge. Attached is my version of the solution.

[RobertOdera]

@agrawaluk 

[agrawaluk]

Hi @TomWelgemoed 

Is there a way to do this for every new ID in the data? For Ex my data looks like this:

Date	ID	Net Cashflow
3/16/2020	1	0
3/17/2020	1	8
3/18/2020	1	7
3/19/2020	1	6
3/20/2020	2	0
3/21/2020	2	4
3/22/2020	2	6
3/23/2020	2	3

and should look like this:

Date	ID	Opening Balance	Net Cashflow	Closing Balance
3/16/2020	1	0	0	0
3/17/2020	1	0	8	8
3/18/2020	1	8	7	15
3/19/2020	1	15	6	21
3/20/2020	2	0	0	0
3/21/2020	2	0	4	4
3/22/2020	2	4	6	10
3/23/2020	2	10	3	13

[AbhilashR]

Hi @agrawaluk - I modified my workflow to accommodate for multiple ID's and reset the calc. for every new ID. Take a look and let us know if this helps.

[Jonathan-Sherman]

Hi @agrawaluk,

You could do this with the use of two multi-row formula tools

Just need to tick ID in the group by section

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[agrawaluk]

Hi @AbhilashR 

Thank you for your response. Actually I guess I stated incorrectly in my previous post. What I am looking at is:

Date	ID	Net Cashflow
3/16/2020	1	0
3/17/2020	1	8
3/18/2020	1	7
3/19/2020	1	6
3/16/2020	2	0
3/17/2020	2	4
3/18/2020	2	6
3/19/2020	2	3

PS - Here Net cashflow for minimum date for every unique ID should be 0. This is something that needs to be implemented in workflow.

What I really want: 

Date	ID	Net Cashflow
3/16/2020	1	0
3/17/2020	1	8
3/18/2020	1	7
3/19/2020	1	6
3/20/2020	2	0
3/21/2020	2	4
3/22/2020	2	6
3/23/2020	2	3

Sorry about that. 

[AbhilashR]

@agrawaluk - In other words no matter what, the Netcashflow for an ID's minimum date should be adjusted to 0 before all other calcs. take place? If yes, I have modified my workflow to include the logic.