Balance checks using SUM

Balance checks are used to validate that the data exported from bank statements is correct. They are quick and easy to apply, although there are a couple of points that you need to be careful of:

- Is the balance calculated before or after the transaction on the same line?

- Are there spaces or text within the debit, credit of balance columns?

- Does the page date order match the transaction date order (in other words, does the balance flow from one page to the next?

The above points will become clear as soon as you start to copy the balance check formula across many transactions, and you will need to modify the formula accordingly.

Start in a separate column. The first row should equal the opening balance.

Then, use this format to construct the balance check:

=SUM( previous calculated balance , - transaction debit , transaction credit )

Finally, copy the formula down to all rows with a transaction.

You can then check the new calculated balance with the balance read from the page. For many transactions, you can use the following formula in a separate column to only display warnings where there is a difference between the calculated balance and the extracted balance.

=IF ( ABS( calculated balance - extracted balance ) >0.01 , "WARNING" , "" )

#Formula #Excel #All