Excel Formulas for Accounting Bank Reconciliation with IF, ISNUMBER and SUM Functions - EMT 1921

Learn to create efficient Excel formulas for accounting bank reconciliation using IF, ISNUMBER, and SUM functions in this 11-minute tutorial. Master the fundamental challenges of tracking bank account balances and discover why bank reconciliation is crucial for accounting accuracy. Explore data validation dropdown lists to ensure proper reconciliation column entries, then build two essential formulas: one to calculate "My Book Balance" using SUM, IF, and ISNUMBER functions, and another to determine "Bank Balance" with SUM, ISNUMBER, and nested IF functions. Discover a clever SUM function technique to reference previous bank balances using relative cell references while avoiding #VALUE! errors. Practice the "Angry Rabbit (Crosshair)" tricks for efficient formula copying and cell referencing. Understand how to automatically reconcile accounts with Excel formulas, creating running totals and implementing nested functions for comprehensive bank reconciliation workflows.

Syllabus

1. Introduction.
2. Fundamental problem with tracking bank account balances, and thus why bank reconciliation is such an important accounting task.
3. Data Validation dropdown list feature to verify that only a y value can be added to the reconciliation column.
4. #1 Formula to calculate “My Book Balance” using the SUM, IF, and ISNUMBER functions.
SUM function trick to reference the previous bank balance Relative Cell Reference and not get a #VALUE! Error.
5. Angry Rabbit Crosshair Trick #1.
6. Angry Rabbit Crosshair Trick #2.
7. #2 Formula to calculate “Bank Balance” using the SUM, ISNUMBER, and two back-back nested IF functions.
8. Angry Rabbit Crosshair Trick #3.
9. Next video: Dynamic Spilled Array Formula.
10. Closing.