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

AkisM · 2021-05-20T12:50:34+00:00

My input Data looks like this: Starting DateEnd DateBeginning BalanceAdditionsDisposalsClosing Balance2020-01-13 0650000002020-02-012020-02-2800002020-03-012020-03-3100002020-04-012020-04-3000002020-05-17 0020000002020-05-172020-05-31010000002020-06-012020-06-300000 As you can see I have the rows I need and the…

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.

Workflow

Transformation

Accepted answers

Luke_C

Hi @AkisM

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

I transpose the data so all of balances/additions/disposals are in one column
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.

New Workflow2.yxmd

All comments

Luke_C

Hi @AkisM

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

I transpose the data so all of balances/additions/disposals are in one column
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.

New Workflow2.yxmd

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.

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335