Calculate sum of totals and insert row with difference

I have 2 columns of data, debits and credits. As per the rules of accounting, total debits and credits must match. I need to sum the 2 columns and figure out the difference and add a row with the difference. Any ideas how to do this?

I manually calculated and concatenated the balancing row like this;

AcctBalance.knwf (26.5 KB)

Here is a running total with calculated differences along the way, as well as a final sum row with calculated differences. That way it may make it easier to visually see where a missing side of a transaction occurs, if the problem arises. If it is a deeper dive / larger scale forensic accounting project, then I tend to group everything by transaction number / contract ID and build a system that identifies the accounting anomalies for me.

AcctBalance.knwf (40.6 KB)

This one has a very basic example of how you can use grouping to help quickly locate possible accounting issues. I also tend to use the rule based splitter a lot to narrow down on transaction IDs that match certain criteria like involving specific accounts or values during a specific date range.

Thank you so much! I took your example and expanded it a little and it works perfect! I included my enhancements for reference.
AcctBalance_Enhanced.knwf (31.1 KB)


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.