Balance of a split transaction that is remaining to allocated

albert_de

New Member
Joined
Mar 16, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Split remaining.JPG
    Split remaining.JPG
    51.5 KB · Views: 25

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 16, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Solution A.JPG
    Solution A.JPG
    102.8 KB · Views: 8

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1payeeRemainingdebitcredit
2a25100
325
425
525
6
7b3575
810
9
1027
113
12c
13d
Sheet1
Cell Formulas
RangeFormula
E2,E7E2=2*SUM(F2:G2)-SUM(F2:INDEX(G3:G$20,MATCH(TRUE,INDEX(A3:A$20<>"",0),0)-1))
 

albert_de

New Member
Joined
Mar 16, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

albert_de

New Member
Joined
Mar 16, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

albert_de

New Member
Joined
Mar 16, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMN
18DateTx TypePayeeCategorysplit… /remainingMemoOutflowInflowBalanceData Validation
19aSplit (Multiple Categories)…Split...100.00100.00100.00Split (Multiple Categories)…split
20split35.0065.00100.00
21split0.0035.00100.00
22bFuel  15.0085.00
23cSplit (Multiple Categories)…Split...-50.0050.0035.00
24split-37.0013.0035.00
25split-9.0028.0035.00
26split0.009.0035.00
27dSplit (Multiple Categories)…Split...135.00135.00170.00
28split60.0075.00170.00
29split0.0060.00170.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
RangeFormula
F27:F50,F19:F25F19=IF(E17="Split (Multiple Categories)…","split",IF(E18="Split (Multiple Categories)…","split",IF(E19="Split (Multiple Categories)…","Split...","")))
G19:G50G19=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,"")))),"")
K19K19=SUM(-I19+J19)
K20:K50K20=IF(F20="split",K19,K19-I20+J20)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I19:J50Expression=$F19="split"textNO
F19:F50Expression=$F19="split"textNO
K19:K50Expression=$F19="split"textNO
Cells with Data Validation
CellAllowCriteria
E19:E50List=$M$18:$M$20
F19:F50List=$N$18:$N$20
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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 (y)

albert_de 2020-03-19 1.xlsm
DEFGHIJK
18PayeeCategorysplit… /remainingMemoOutflowInflowBalance
19aSplit (Multiple Categories)…Split...0100100
20split 65100
21split 35100
22bFuel  1585
23cSplit (Multiple Categories)…Split...375035
24split 1335
25split 35
26split 35
27dSplit (Multiple Categories)…Split...0135170
28split 75170
29split 60170
30  170
31  170
32  170
33  170
34  170
Sheet2
Cell Formulas
RangeFormula
F19:F25,F27:F34F19=IF(E17="Split (Multiple Categories)…","split",IF(E18="Split (Multiple Categories)…","split",IF(E19="Split (Multiple Categories)…","Split...","")))
G19:G34G19=IF(F19="Split...",2*SUM(I19:J19)-SUM(I19:INDEX(J20:J$60,MATCH(TRUE,INDEX(F20:F$60<>"split",0),0)-1)),"")
K19K19=SUM(-I19+J19)
K20:K34K20=IF(F20="split",K19,K19-I20+J20)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,685
Messages
5,626,293
Members
416,172
Latest member
lordposh

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top