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 removing all but Payee A in your table to demonstrate the problem. In reality there may not be a new transaction to add after the transaction split to enable the remaining calculation. the "remaining" balance in Column E doesn't function until a new item is added in the Payee Column (A).Not sure if this helps to clairfy what I am trying to achieve or not?!!Īfter migrating the formula into the actual spreadsheet I noticed a few problems: 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 I added an IFERROR as an unhelpful Value error is returned under certain conditions that I can't remember now. A running remaining (positive or negative) balance is returned against each split in Column G until it hits " split" again or a blank cell. 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. In the "remaining" column (G) I have used the following formula commencing in G5 I attach a screenshot of my revised attempt at this problem ("Solution A.jpg). E2 & E7 of the "Split remaining.jpg" screenshot) But I have not been able to achieve displaying the result in a single cell in the "base" transaction row (e.g. In the meantime I have come up with a workaround. I guess what I am trying to achieve this is not an easy thing to explain! 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 am also trying to create a solution smart enough to catch entry errors (e.g. I intend that each table (or account ledger) will span from Jan 1 to Dec 31. And to do this again when the next split transaction occurs.
#Evan schwartz multiple jumps transaction ledgers how to#
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. This is a split transaction.Ī split transaction can occur at anytime throughout the table, and each will contain a variable number of splits. the sum of each should total the original single base transaction. 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). A split transaction will only be known at the time the transaction or purchase is made.Īs you probably know, a Split transaction in a budget is for example: It cannot be know in advance when a split transaction occurs in the ledger or how many splits there maybe against that transaction. But in the case of a ledger the range is dynamic. =SUM(F2:G2)-SUM(F3:G6), implies that the range is fixed. I understand that I may not have ask my question very well! I expect that the solution (if there is one) is a bit more complex. Thank you for having a think about my question. I would be grateful for any help as I am truly stuck. Hopefully someone much smarter than me can teach me how to resolve this task. 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. (Split amounts do not affect the account balance, only the base transaction affects the account balance) The split amount is placed in either column F & G. 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. 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). My goal is to be able to report how much of the base transaction split remains to be allocated. Hi, I am trying to build a bank account ledger in excel that allows a transaction to be split across multiple (budget) categories.
0 kommentar(er)
