If your accounting package doesn’t let you apply g/l entries to other g/l entries, or if you just haven’t been doing it, reconciliation of balance sheet can often be an arduous task, with dozens, hundreds or thousands of line to match before you can find out your outstanding items.
The following formulas will help you make a first pass of reconciliation before you dig in further. The goal is to take care of all the one-for-one reversals. To find them, we check the amount column, and for each line, we check if the sum of amounts matching that line’s amount, either with a negative or positive sign, is equal to zero.
First, the two step method, using table references rather than range references. If the [ ] and [@..] confuse you, check out this tutorial on office.microsoft.com.
- Create a column with the absolute value of the line’s amount, using the ABS() function: =ABS([@Amount])
- Create a column with a formula summing the Amount column if the Absolute values match: =SUMIF([ABS],[@ABS],[Amount])=0
Second the one step method, using sumproduct:
Both of these methods will return a list of TRUE / FALSE values: TRUE if the amount is matched, FALSE if it isn’t. You can filter out the TRUE value and focus your attention on the FALSE. Your work isn’t done yet, but it got started faster. Check out the example file (quick balance sheet matching) to see it in action.
Try it, you might like it,