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.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
