Balance of a split transaction that is remaining to allocated

[albert_de]

Hi, I am trying to build a bank account ledger in excel that allows a transaction to be split across multiple (budget) categories. My goal is to be able to report how much of the base transaction split remains to be allocated.

I have no problem achieving this using a horizontal table, but I cannot figure out how to achieve this using a vertical table (as per a normal ledger).

In the attached screenshot, E2 is trying to calculate the balance of the split that remains to be allocated for the base transaction shown in G2 based on rows allocated with "split" in column C. The split amount is placed in either column F & G. (Split amounts do not affect the account balance, only the base transaction affects the account balance)

The issue I have is working out how to get Excel to calculate in columns until there is a change in another column (in this case column C to return the split remaining to be allocated.

Hopefully someone much smarter than me can teach me how to resolve this task. I would be grateful for any help as I am truly stuck. Thank you.

 

[Rijnsent]

Hi Albert,
I'm not sure I understand your question, but my simpleton mind says just insert a formula like: =SUM(F2:G2)-SUM(F3:G6)
Is that what you're looking for?
Cheers,
Koen

 

[albert_de]

Hi Koen,
Thank you for having a think about my question. I appreciate it.

I understand your solution.

I expect that the solution (if there is one) is a bit more complex. I understand that I may not have ask my question very well!

=SUM(F2:G2)-SUM(F3:G6), implies that the range is fixed. But in the case of a ledger the range is dynamic. It cannot be know in advance when a split transaction occurs in the ledger or how many splits there maybe against that transaction. A split transaction will only be known at the time the transaction or purchase is made.

As you probably know, a Split transaction in a budget is for example:
you buy items at a Shell Petrol Station in a single transaction (totaling $100.00), but you may want to take money out from different accounts/categories/budgets e.g Petrol budget ($70.00), Food budget ($20.00), Car Maintenance budget ($10.00). the sum of each should total the original single base transaction. This is a split transaction.

A split transaction can occur at anytime throughout the table, and each will contain a variable number of splits.

Referring to my original "Split remaining.jpg" screenshot, I am trying to work out how to define a range when certain conditions are met, that is, define when each Split transaction starts and ends and calculate the remaining balance to be allocated for that that transaction. And to do this again when the next split transaction occurs.

I intend that each table (or account ledger) will span from Jan 1 to Dec 31.

I am also trying to create a solution smart enough to catch entry errors (e.g. as per the "Split remaining.jpg" screenshot - where an entry is missed, or if the transaction is a debit and the split entry is entered as a credit etc.)

I guess what I am trying to achieve this is not an easy thing to explain!

In the meantime I have come up with a workaround. But I have not been able to achieve displaying the result in a single cell in the "base" transaction row (e.g. E2 & E7 of the "Split remaining.jpg" screenshot)

I attach a screenshot of my revised attempt at this problem ("Solution A.jpg).

In the "remaining" column (G) I have used the following formula commencing in G5
=IF(F5="split...",-I5+J5,IF(F5="split",G4+I5-J5,IF(AND(F4="split...",F5="",F6="split"),G4,IF(AND(F4="split",F5="",F6="split"),G4,""))))

This formula looks for the text "split..." and "split" criteria (where "Split.." identifies the base transaction value and "split" identifies the base transaction split value) in column F. A running remaining (positive or negative) balance is returned against each split in Column G until it hits "split" again or a blank cell.

I added an IFERROR as an unhelpful Value error is returned under certain conditions that I can't remember now.

This solution works. It may not be the most elegant or practical and it is not quite what I am trying to achieve (the "amount remaining to be allocated" result in a single cell instead of multiple cells as per the Solution A jpg

Not sure if this helps to clairfy what I am trying to achieve or not?!!

Thank you again for your thoughts.

Cheers
Richard

 

[Peter_SSs]

Welcome to the MrExcel board!

You will generally get more people looking, & therefore likely faster/more suggestions, if you provide your sample data in a form that can be copied as most helpers are too keen on typing it out manually.
Check out XL2BB

See if you can make some use of this.

albert_de 2020-03-19 1.xlsm
	A	B	C	D	E	F	G
1	payee				Remaining	debit	credit
2	a				25		100
3							25
4							25
5							25
6
7	b				35	75
8						10
9
10						27
11						3
12	c
13	d
Sheet1

Cell Formulas
Range	Formula
E2,E7	E2	=2*SUM(F2:G2)-SUM(F2:INDEX(G3:G$20,MATCH(TRUE,INDEX(A3:A$20<>"",0),0)-1))

 

[albert_de]

Hi Peter,

Thank you for your reply and welcome. Greatly appreciated.

I will investigate XL2BB , as this makes a lot of sense (of course).

Thank you for your solution. It looks very promising. I will try this in my test spreadsheet and try applying it in my actual spreadsheet once I have fully learnt your solution.

I will post an update after this.

Thank you very much again for your help.

Cheers

 

[Peter_SSs]

Thank you very much again for your help.

You're welcome. Hope it works out for you.

 

[albert_de]

Hi Peter

Your formula works a treat. Excellent. Thank you

I added an IF statement to the front so that I could autofill all of Column E and return "" for every cell except for the first cell in the split:
=IF(C2<>"split...","",2*SUM(F2:G2)-SUM(F2:INDEX(G3:G$20,MATCH(TRUE,INDEX(A3:A$20<>"",0),0)-1)))

I dont quite understand all of your formula yet so I need to do some homework so I can using principle again when required. I love Excel and I have been using it since the 90's but I still have some to learn!

Cheers and thank you.
Richard

 

[Peter_SSs]

Hi Peter

Your formula works a treat. Excellent. Thank you

You're welcome.

I added an IF statement to the front so that I could autofill ..

That sounds reasonable too.

.. but I still have some to learn!

We are all in that situation. It is what keeps Excel interesting!

 

[albert_de]

Hi Peter

After migrating the formula into the actual spreadsheet I noticed a few problems:

• the "remaining" balance in Column E doesn't function until a new item is added in the Payee Column (A). In reality there may not be a new transaction to add after the transaction split to enable the remaining calculation. Try removing all but Payee A in your table to demonstrate the problem.
• If the split transaction is based on a Credit transaction (as per the Payee A transaction in your table), and a split is accidentally entered as a Debit, the formula will include the error when calculating the balance. Try moving one of the $25 credits to the debit column to demonstrate.

I have included a partial mock up of my acutal ledger using the workaround solution that I noted in my reply to Koen (thanks XL2BB!). It doesn't provide the single cell "remaining" balance, but it addresses the 2 problems noted above. It might provide a range that can be used instead of Column A for the MATCH/INDEX and a way to address the CREDIT/DEBIT problem?

I will keep looking at it to see if I can modify your formula but I would be grateful for your thoughts

Thank you Peter

Cheers
Richard

split alternative thinking v2.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
18		Date	Tx Type	Payee	Category	split… /	remaining	Memo	Outflow	Inflow	Balance		Data Validation
19				a	Split (Multiple Categories)…	Split...	100.00			100.00	100.00		Split (Multiple Categories)…	split
20						split	35.00			65.00	100.00
21						split	0.00			35.00	100.00
22				b	Fuel				15.00		85.00
23				c	Split (Multiple Categories)…	Split...	-50.00		50.00		35.00
24						split	-37.00		13.00		35.00
25						split	-9.00		28.00		35.00
26						split	0.00		9.00		35.00
27				d	Split (Multiple Categories)…	Split...	135.00			135.00	170.00
28						split	60.00			75.00	170.00
29						split	0.00			60.00	170.00
30											170.00
31											170.00
32											170.00
33											170.00
34											170.00
35											170.00
36											170.00
37											170.00
38											170.00
39											170.00
40											170.00
41											170.00
42											170.00
43											170.00
44											170.00
45											170.00
46											170.00
47											170.00
48											170.00
49											170.00
50											170.00
Sheet2

Cell Formulas
Range	Formula
F27:F50,F19:F25	F19	=IF(E17="Split (Multiple Categories)…","split",IF(E18="Split (Multiple Categories)…","split",IF(E19="Split (Multiple Categories)…","Split...","")))
G19:G50	G19	=IFERROR(IF(F19="split...",-I19+J19,IF(F19="split",G18+I19-J19,IF(AND(F18="split...",F19="",F20="split"),G18,IF(AND(F18="split",F19="",F20="split"),G18,"")))),"")
K19	K19	=SUM(-I19+J19)
K20:K50	K20	=IF(F20="split",K19,K19-I20+J20)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I19:J50	Expression	=$F19="split"	text	NO
F19:F50	Expression	=$F19="split"	text	NO
K19:K50	Expression	=$F19="split"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E19:E50	List	=$M$18:$M$20
F19:F50	List	=$N$18:$N$20

 

[Peter_SSs]

After migrating the formula into the actual spreadsheet I noticed a few problems:

• the "remaining" balance in Column E doesn't function until a new item is added in the Payee Column (A). In reality there may not be a new transaction to add after the transaction split to enable the remaining calculation. Try removing all but Payee A in your table to demonstrate the problem.
• If the split transaction is based on a Credit transaction (as per the Payee A transaction in your table), and a split is accidentally entered as a Debit, the formula will include the error when calculating the balance. Try moving one of the $25 credits to the debit column to demonstrate.

In relation to the first point, let's try using the "split.../" column instead, see below.
For the second point I take your point but want to clarify what action you want if any entries are made in the 'opposite' column. In my experience a debit entry of $150 might be made up of two purchases of $100 and a refund (credit) of $50 so split entries would be allowed in both columns, though I would have to calculate the remaining balance differently. Or do you want to flag some sort of error if entries are made in the 'wrong' column?

BTW, thanks for posting with XL2BB

albert_de 2020-03-19 1.xlsm
	D	E	F	G	H	I	J	K
18	Payee	Category	split… /	remaining	Memo	Outflow	Inflow	Balance
19	a	Split (Multiple Categories)…	Split...	0			100	100
20			split				65	100
21			split				35	100
22	b	Fuel				15		85
23	c	Split (Multiple Categories)…	Split...	37		50		35
24			split			13		35
25			split					35
26			split					35
27	d	Split (Multiple Categories)…	Split...	0			135	170
28			split				75	170
29			split				60	170
30								170
31								170
32								170
33								170
34								170
Sheet2

Cell Formulas
Range	Formula
F19:F25,F27:F34	F19	=IF(E17="Split (Multiple Categories)…","split",IF(E18="Split (Multiple Categories)…","split",IF(E19="Split (Multiple Categories)…","Split...","")))
G19:G34	G19	=IF(F19="Split...",2*SUM(I19:J19)-SUM(I19:INDEX(J20:J$60,MATCH(TRUE,INDEX(F20:F$60<>"split",0),0)-1)),"")
K19	K19	=SUM(-I19+J19)
K20:K34	K20	=IF(F20="split",K19,K19-I20+J20)