Join 400,000+ professionals in our courses here 👉 https://link.xelplus.com/yt-d-all-courses
A common task for finance professionals is to create YTD (Year-To-Date) calculations in Microsoft Excel. Usually these formulas can get quite complex. You might use a combination of SUMPRODUCT and OFFSET. In this video I'll show you a solution using the new Excel SCAN function. We create a flexible YTD function so that you can easily adjust for fiscal months. If you get more data, the Year to date calculations automatically update.
⬇️ Download the workbook here: https://pages.xelplus.com/scan-function-file
Discover how to:
⩥ Use the SCAN function for simple to-date calculations, allowing formulas to automatically spill down without manual dragging.
⩥ Integrate LAMBDA functions with SCAN for custom calculations, enabling you to process data with precision.
⩥ Dynamically calculate YTD values, adjusting for new calendar years within your data set to reset accumulations accurately.
⩥ Apply practical examples with sales data to see the SCAN function in action, demonstrating both to-date and YTD calculations.
⩥ Adjust for fiscal calendars, showing how to modify the formula for different fiscal year starts, ensuring versatility across various accounting periods.
We'll combine the SCAN, LAMBDA and Excel OFFSET functions to create a (simple) formula that dynamically updates your results and resets whenever it crosses a year-end.
Note: The SCAN function is available in Microsoft 365.
🌍 My Online Excel Courses ► https://www.xelplus.com/courses/
00:00 Calculate YTD Balances Dynamically in Excel
00:41 How to Calculate To-Date With the SCAN Function
02:42 How the Excel SCAN Function Works
03:59 How to Calculate Year-To-Date With the SCAN Function
07:53 Wrap Up
🎬 LINKS to related videos:
Excel LAMBDA Function: https://youtu.be/Rm4y5UqauRw
Excel OFFSET Function: https://youtu.be/RPTQjbk2qy4
🎒 Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
📚 Excel RESOURCES I Recommend: https://www.xelplus.com/resources/
🎥 GEAR I use: https://www.xelplus.com/resources/#gear
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel