Premium Only Content

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
-
5:46
Buddy Brown
23 hours ago $0.19 earnedOklahoma Just Made the BOLDEST MOVE of our Time! | Buddy Brown
9.23K5 -
2:01:04
Steven Crowder
4 hours agoThe Gavin Newsom 2028 Campaign Begins... And It Sucks
260K165 -
11:25
Mike Rowe
1 day agoThe Funniest Mashup You've Ever Seen With My Mom, Peggy Rowe | Coffee With Mom
6.37K8 -
1:23:33
Steve-O's Wild Ride! Podcast
6 days ago $13.78 earnedRainn Wilson and Steve-O Break The Silence On Their Long Friendship | Wild Ride #263
42K30 -
1:00:17
VINCE
5 hours agoThe Trump Admin Isn't Having This... | Episode 107 - 08/20/25
203K119 -
LIVE
JuicyJohns
5 hours ago $1.84 earned🟢#1 REBIRTH PLAYER 10.2+ KD🟢
488 watching -
1:45:07
Dear America
5 hours agoAndrew Bailey is now Bongino’s CO-DEPUTY DIRECTOR of the FBI!! Is Bongino On His Way Out??
101K70 -
3:18:47
Reidboyy
3 hours ago $0.35 earnedNEW FREE FPS OUT ON CONSOLE TODAY! (Delta Force = BF6 with Killstreaks)
18.2K -
2:19:32
Matt Kohrs
13 hours agoMore Pain Incoming?! || LIVE! Trading Futures & Options
46.4K2 -
26:53
Law&Crime
19 hours ago $2.06 earnedTikTok Teen Sarah Grace Patrick Sobs in Dramatic Court Meltdown
22.2K6