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

albert_de

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

Thank you once again for your help today.

I have been busy trying learn more about INDEX and MATCH but I think the 39 degree day in Sydney is putting me to sleep!

I came up with a similar approach to the first point and then fell back onto what is probably bad habits with point 2 and got stuck.

To answer your question re point 2 - I take your point about split transactions possibly including a Debit and a Credit. At this stage I think I would be happy if I could work out how to look ay Column I & J independently.

With that in mind I tried the following 2 separate formulas in different split transactions (a Debit and a Credit):
Debit:
=IF(F19<>"split...","",2*SUM(I19:I19)-SUM(I19:INDEX(I20:I$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),0)-1)))

Credit
=IF(F19<>"split...","",2*SUM(J19:J19)-SUM(J19:INDEX(J20:J$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),0)-1)))

Separately I am happy with the way this works -only the column with the transaction amount in it is calculated, the other column is ignored.

How to solve this?

This is where I fell into a bad habit that probably doesn't work in this situation by trying to nest the above formula in G19 down. formula.
=IF(F19<>"split...","",IF(I19>0,2*I19-SUM(I19:INDEX(I20:I$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),IF(J19>0,2*J19-SUM(J19:INDEX(J20:J$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),0)))))))))

Doesn't work. I guess nesting doesn't work here?

I hope that makes some sense!

Cheers
Richard
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
To be honest, giving formulas that you have tried and don't quite do what you want is not what is required here. Whilst it is showing that that you are trying to solve the problem yourself (which is great to see), it doesn't help get to the desired result.

What would help me most to help you, is to have 6 or 8 smallish split transactions (& non-split transactions) that demonstrate the different input possibilities (eg entries in the wrong column, missing a row, totals that are equal to, greater than and less than the original transaction amount) and the desired results.
 

albert_de

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

OK, thanks.

Please see below as requested.

Thank you once again.

Cheers
Richard

split alternative thinking v2.2.xlsx
BCDEFGHIJKLMNOP
18DateTx TypePayeeCategorysplit… /remainingMemoOutflowInflowBalance
191/01/2020Direct CreditPaySplit (Multiple Categories)…Split...0.00100.00100.00Split (Multiple Categories)…split
20Category Asplit 65.00100.00Category A
21Category Bsplit 35.00100.00Category B
222/01/2020EFTPOSShellFuel  15.0085.00Category C
233/01/2020EFTPOSBunningsSplit (Multiple Categories)…Split...37.0050.0035.00Category D
24Category Asplit 13.0035.00
25Category Bsplit 28.0035.00
26Category Csplit 9.0035.00
274/01/2020Direct CreditCBASplit (Multiple Categories)…Split...0.00135.00170.00
28Category Csplit 75.00170.00
29Category Dsplit 60.00170.00
305/01/2020EFTPOSColesCategory A  20.00150.00
316/01/2020DepositBruce WillisCategory C  1,000,000.001,000,150.00
327/01/2020BPAYATOCategory C  750,000.00250,150.00
338/01/2020EFTPOSHappy DollarSplit (Multiple Categories)…Split...1.9928.64250,121.36
34Category Bsplit 1.99250,121.36
35Category Csplit 26.65250,121.36
369/01/2020EFTPOSSydney MarketsCategory C  125,000.36125,121.00
3710/01/2020Direct CreditHarry PotterSplit (Multiple Categories)…Split...79.00100.00125,221.00
38Category Asplit 65.00125,221.00
39Category Bsplit 21.00125,221.00
40Category Dsplit 14.00125,221.00
41  125,221.00
42  125,221.00
43  125,221.00
Sheet2
Cell Formulas
RangeFormula
F41:F43,F27:F39,F19:F25F19=IF(E17="Split (Multiple Categories)…","split",IF(E18="Split (Multiple Categories)…","split",IF(E19="Split (Multiple Categories)…","Split...","")))
G27:G29,G19:G21G19=IF(F19<>"split...","",2*J19-SUM(J19:INDEX(J20:J$50,MATCH(TRUE,INDEX(F20:F$50<>"split",0),0)-1)))
G22,G32G22=IF(F22<>"split...","",2*SUM(I22:I22)-SUM(I22:INDEX(I23:I$37,MATCH(TRUE,INDEX(F23:F$37<>"split",0),0)-1)))
G33:G36,G30:G31,G23:G26G23=IF(F23<>"split...","",2*I23-SUM(I23:INDEX(I24:I$50,MATCH(TRUE,INDEX(F24:F$50<>"split",0),0)-1)))
G37G37=IF(F37<>"split...","",2*J37-SUM(J37:INDEX(J$37:J50,MATCH(TRUE,INDEX(F$37:F50<>"split",0),0)-1)))
G38:G40G38=IF(F38<>"split...","",2*J38-SUM(J38:INDEX(J$37:J39,MATCH(TRUE,INDEX(F$37:F51<>"split",0),0)-1)))
G41:G43G41=IF(F41<>"split...","",2*SUM(J41:J41)-SUM(J41:INDEX(J$37:J42,MATCH(TRUE,INDEX(F$37:F42<>"split",0),0)-1)))
K19K19=SUM(-I19+J19)
K20:K43K20=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=$O$18:$O$24
F19:F50List=$P$18:$P$20
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
If I have understood correctly you could try this

albert_de 2020-03-19 1.xlsm
FGHIJ
18split… /remainingMemoOutflowInflow
19Split...0100
20split 65
21split 35
22 15
23Split...3750
24split 13
25split 28
26split 9
27Split...0135
28split 75
29split 60
30 20
31 1000000
32 750000
33Split...1.9928.64
34split 1.99
35split 26.65
36 125000.36
37Split...79100
38split 65
39split 21
40split 14
41 
42 
43 
44 
Sheet5
Cell Formulas
RangeFormula
G19:G44G19=IF(F19<>"split...","",2*SUM(I19:J19)-SUM(IF(I19="",J19:INDEX(J19:J$50,MATCH(TRUE,INDEX(F20:F$50<>"split",0),0)),I19:INDEX(I19:I$50,MATCH(TRUE,INDEX(F20:F$50<>"split",0),0)))))



Or if you don't have a huge data set (that is, not tens of thousands of rows) then you could also try this.
Because it uses the volatile function OFFSET, if used a huge amount of times in a sheet it can affect the speed of the sheet calculation.

albert_de 2020-03-19 1.xlsm
FGHIJ
18split… /remainingMemoOutflowInflow
19Split...0100
20split 65
21split 35
22 15
23Split...3750
24split 13
25split 28
26split 9
27Split...0135
28split 75
29split 60
30 20
31 1000000
32 750000
33Split...1.9928.64
34split 1.99
35split 26.65
36 125000.36
37Split...79100
38split 65
39split 21
40split 14
41 
42 
43 
44 
Sheet6
Cell Formulas
RangeFormula
G19:G44G19=IF(F19<>"split...","",2*SUM(I19:J19)-SUM(OFFSET(I19,0,IF(I19="",1,0),MATCH(TRUE,INDEX(F20:F$50<>"split",0),0))))
 

albert_de

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

ADVERTISEMENT

Hi Peter

Thank you for time and patience. The formula works perfectly.

I see where I went wrong with nesting the formula's.

Thank you very much for helping me with this task. I really appreciate it.

Cheers
Richard
 

anthonyfca

New Member
Joined
Jan 12, 2012
Messages
8

ADVERTISEMENT

I can see this is an old question, but it relates to my own one-to-many ledger problem so I am looking at it for inspiration before asking my own question, in the meantime I came up with this so may as well offer it.
My answer would be to fill in the payee column fully and then use this formula in column D to give the rolling remaining amount per base amount:
Excel Formula:
=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)

and here shown:
splitz.xlsx
D
2100.00
Sheet1 (2)
Cell Formulas
RangeFormula
D2D2=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)


I now see it doesn't help my problem, but there we are, it was fun :)

Anthony
 

anthonyfca

New Member
Joined
Jan 12, 2012
Messages
8
sorry, new at this and still can't find the edit button:
splitz.xlsx
ABCDEFGH
1payeecategorysplitremainingmemodebitcreditbalance
2c/fSplit (Multiple Categories)split100.00100.00100.00
3c/fasplit75.0025.00
4c/fbsplit50.0025.00
5c/fcsplit25.0025.00
6c/fd0.0025.00
7aldiSplit (Multiple Categories)split(6.00)(6.00)94.00
8aldiasplit(5.00)(1.00)
9aldibsplit(3.00)(2.00)
10aldicsplit0.00(3.00)
11shellb10.0010.0084.00
12tex mexSplit (Multiple Categories)split1,000.0010001084
13tex mexasplit978.7021.30
14tex mexbsplit969.079.63
15tex mexcsplit99.40869.67
16tex mex0.0099.40
17paymanual splitView Split1,500.002,584.00
Sheet1 (2)
Cell Formulas
RangeFormula
D2:D16D2=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,465
Messages
5,624,902
Members
416,064
Latest member
PaulBr2

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