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
-
LIVE
Lofi Girl
2 years agoSynthwave Radio 🌌 - beats to chill/game to
178 watching -
22:39
BlabberingCollector
7 hours agoThe Alphabet Mafia Is Mad At JK Rowling AGAIN
12.2K2 -
5:30:52
SpartakusLIVE
9 hours agoDuos w/ @GloryJean || #1 Masculine Muscle MASS sears YOUR retinas with MIND BENDING content
219K3 -
3:05:49
TimcastIRL
8 hours agoNew DOCS PROVE Obama Hillary CONSPIRACY To SABOTAGE Trump Admin | Timcast IRL
217K94 -
2:29:36
Laura Loomer
8 hours agoEP136: YOU'RE FIRED! White House Vetting Crisis Continues
58.8K39 -
8:07
MattMorseTV
8 hours ago $6.31 earnedTrump just LOWERED PRICES by 75 PERCENT.
35.6K34 -
LIVE
Misfit Electronic Gaming
11 hours ago $4.37 earned"LIVE" "Blind Descent' +"Dollhouse of Dead" Playtest 10 Followers till we hit 1000! We CAN do this!
525 watching -
21:53
Glenn Greenwald
10 hours agoMichael Tracey on the Street: What Do People Think of the Epstein Case?
127K58 -
2:26:28
megimu32
7 hours agoOTS: Board Games Gone Wild! The Loud, Weird & Chaotic Games That Raised Us
37.6K9 -
4:25:16
DamnDanieI
8 hours agoKill First, Loot Later – OTG Live
55.3K1