Excel Dynamic YTD Calculations: OFFSET, SUMPRODUCT & SUM

Dec 1, 2016 Excel Advanced Formulas & Features
This video shows you how to write dynamic YTD formulas in Excel using SUMPRODUCT, OFFSET & SUM functions.

Blogpost: http://www.xelplus.com/sumproduct-year-to-date/

Get the full course: https://courses.xelplus.com/

Lecture Summary:

In this video, I'll show you a way of writing YTD formulas (in this specific case for volume and price) when your data set goes over a few years. i.e. each month occurs more than once. If you were doing this manually, you'll need to revise your formula in the middle of your data set to make your range start from January of the next year. OFFSET allows you to create dynamic ranges - when you use this with the Month() and SUM() functions, you've created a smart formula that recognizes when the data goes over to the next year and revises your YTD calculation.

Calculating, YTD price can be a bit trickier. To get the volume weighted average price, you need to multiply the volume by the price and add each month to the next up to the current month. Then divide that value by the YTD volume.

This is when knowing how to use Excel's advanced formulas together can become helpful. Here the SUMPRODUCT function together with OFFSET can come to the rescue.

★ My Online Excel Courses ► https://courses.xelplus.com/

✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593

GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/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!

#MsExcel