How to iteratively calculate values both horizontally and vertically at the same time?

[AkisM]

My input Data looks like this:

Starting Date	End Date	Beginning Balance	Additions	Disposals	Closing Balance
2020-01-13		0	650000	0	0
2020-02-01	2020-02-28	0	0	0	0
2020-03-01	2020-03-31	0	0	0	0
2020-04-01	2020-04-30	0	0	0	0
2020-05-17		0	0	200000	0
2020-05-17	2020-05-31	0	10000	0	0
2020-06-01	2020-06-30	0	0	0	0

As you can see I have the rows I need and the transactions that happened but all the calculated fields are 0. I need to first perform a calculation horizontally (Beginning Balance + Additions - Disposals = Closing Balance) and then carry that Closing Balance to the next row as beginning balance, before performing the same calculation again.

So my target output would look like this:

Starting Date	End Date	Beginning Balance	Additions	Disposals	Closing Balance
2020-01-13		0	650000	0	650000
2020-02-01	2020-02-28	650000	0	0	650000
2020-03-01	2020-03-31	650000	0	0	650000
2020-04-01	2020-04-30	650000	0	0	650000
2020-05-17		650000	0	200000	450000
2020-05-17	2020-05-31	450000	10000	0	460000
2020-06-01	2020-06-30	460000	0	0	460000

Any ideas? I tried with just formulas and multi row formulas but it wouldn't work and I thought a macro may be needed which I'm not very familiar with.

[Luke_C · Accepted solution]

Hi @AkisM 

You could definitely use an iterative macro for this, but here's a potentially simpler, non-macro solution.

1. I transpose the data so all of balances/additions/disposals are in one column
2. I use the multi-row formula tool to look at the data based on the bucket it falls into, i.e.:
   1. Closing balance: row-3 + row-2 - row-1, which translates to beginning + additions - disposals
   2. Beginning balance: take the value of the preceding row (previous closing balance)

Let me know if this makes sense or if it doesn't seem to work for your data.

[AkisM]

Actually it works perfectly fine @Luke_C , thanks a lot! But just for the sake of learning and my curiosity, if you were to use a macro for this, what would it look like? Because even though this transposing solution looks simpler, it's harder for me to wrap my head around than I would understand a macro I think.

[Luke_C]

Hey @AkisM 

Sorry for the delayed reply. Here's an example that shows how this could have been done with a macro:

1. Read in data (prior to this I added record ID starting with 0, which can be seen in the main workflow).
2. Compute ending balance
3. Multi-row formula to update opening balance of the following rows
4. Filter to take the record ID associated with the iteration being processed, the rest loop through and go through the process again.

Let me know if you have any issues or questions.

[AkisM]

Thanks @Luke_C . That's exactly what I had in mind when I first encountered this use case but didn't quite know how to assemble due to my inexperience with iterative macros. That made it clear!