Excel SCAN Function Dynamic Spilled Array Formula for Automatic Bank Reconciliation. EMT 1922.
From excelisfun · · 10 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.
If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.
Description
Download: 1) Excel File: https://excelisfun.net/files/EMT1920-1921-1922.xlsx Buy excelisfun’s Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to create dynamic spilled array formulas and functions. Specifically, learn how to create a dynamic spilled array formula to perform automatic bank reconciliation, an important and common accounting task. Lean about the power of Microsoft's amazing SACN Dynamic Spilled Array Formula to calculate running totals and use a relative cell reference in a dynamic spilled array formula. Topics: 1. (00:00) Introduction 2. (01:12) Single Input, Single Output Formula and how they are different than Dynamic Spilled Array Formulas. 3. (02:20) Why we need to use SCAN function rather than just arrays in the formula, and why Aggregate Formulas can NEVER spill an array of values. 4. (03:21) SCAN function to calculate a Running Total. SCAN function is the Dynamic Spilled Array function that can invoke a Relative Cell Reference in a Dynamic Spilled Array Formula. 5. (04:11) The differences between deleting Single Input, Single Output formulas and Dynamic Spilled Array Formulas. 6. (04:41) SCAN Formula for My Book Balance. 7. (04:57) Dynamic Spilled Array Formulas only live in the top cell. 8. (05:13) Remaining cells contain Ghost Formulas. 9. (05:24) Why Dynamic Spilled Array Formulas are so convenient. 10. (05:39) Referencing Ghost Formulas in a Dynamic Spilled Array. 11. (07:14) Editing a Dynamic Spilled Array Formula. 12. (07:18) IF and ISNUMBER functions. 13. (07:49) Zero Length Text String. 14. (08:09) Test Formula. 15. (08:24) SCAN Formula for Bank Balance. 16. (09:24) Why Omitting value_if_false argument in the IF is efficient. 17. (10:24) Summary. 18. (10:41) Closing #excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #dynamicspilledarrayformulas #spilledarrays #arrayformulas #excelformulas #excelfunctions #excelfunction #scanfunction #byrow #scan #scanarray #excel365 #excel #exceltips #exceltutorial #exceltips #excellent #excellence #bankreconciliation #accounting #accountingtips #debit #credit #isnumber #isnumberfunction #sum #sumfunction #sumformula #excelmagictrick #excelmagictrick
This video has subtitles or audio translations in these languages: Abkhazian, Afrikaans, Albanian, Amharic, Arabic, Armenian, Bangla, Belarusian, Bosnian, Bulgarian, Burmese, Cantonese, Cantonese (Hong Kong), Catalan, Chinese, Chinese (China), Chinese (Hong Kong), Chinese (Simplified), Chinese (Singapore), Chinese (Taiwan), Chinese (Traditional), Corsican, Croatian, Czech, Danish, Dutch, Dutch (Netherlands), Estonian, Faroese, Fijian, Filipino, Finnish, French, French (Belgium), French (Canada), French (France), French (Switzerland), Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Hebrew, Hindi, Hungarian, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean, Kurdish, Lao, Latvian, Lithuanian, Luxembourgish, Macedonian, Maithili, Malagasy, Malay, Malay (Singapore), Malayalam, Maltese, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Portuguese (Portugal), Punjabi (experimental), Romanian, Russian, Sanskrit, Scottish Gaelic, Serbian, Serbian (Cyrillic), Serbian (Latin), Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Tongan, Tsonga, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Vietnamese, Welsh, Western Frisian, Zulu. Click gear icon below video to set subtitles, audio and language.