Excel Formulas: Dynamic YTD Calculations that Work

Q: What's the main problem you come across when you're writing a Year-to-Date (YTD) formula in Excel and your data set goes over a few years?

A: You need to revise it every time you get to January of the next year. You can't just drag your YTD formula that starts in January of 2015 all the way down to January 2017. It needs to be revised every time you get to January.

Take a look at this:

To get YTD Volume you just need to use a simple SUM formula and fix the first cell reference in the sum range. The problem comes when you get to January of the next year. Here, you need to revise the formula again and change the first cell reference to reference the amount in that January like this:

What's the problem with this approach?

1. Prone to Errors: Excel users are generally used to seeing spreadsheets where the first formula in a row (if your data set is vertical) or column (if your data set is horizontal) applies to the cells in the next rows & columns. If your colleague gets a hold of this spreadsheet, they are tempted to drag the formula in cell B11 all the way across to O11.
2. Boring Manual Work: You need to go to each January of every year: Update formula. Drag to December. Change formula in January. Drag to December. Repeat.

Watch this Video to find out how you can write the formula just ONCE in the first cell and drag across for how many years & months you have in your data set.

You'll see that calculating YTD volume is easier than YTD Price which is a bit trickier.

To get the volume weighted average price (VWAP), 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 come in handy. In the video you'll see how SUMPRODUCT and OFFSET assist each other to do the work for you.

The best part about creating dynamic formulas? You just need to type them in once and drag them all the way across!

P.S. If you'd like to become faster in solving complex Excel problems, check out my Excel Advanced: Top Tips & Formulas course. Discount available HERE.