YTD or year to date totals are an important concept when it comes to making analysis and report in the finance domain. In this video let me show you a really simple trick using which you can calculate YTD totals for either a calendar year that is from Jan to December or a financial year that could start from any month maybe in April. Let's take a look at it.
Here I have got a monthly registrations for some sort of a school and i just want to see year to date how many registrations have been there we are going to look at this formula for a cell range based values as well as a tabular data we are also going to look at the mythical offset formula what's the whole thing we can use the if formula to set up the year to date calculations we'll say if month off this cell is equal to 1 here we are assuming our year starts in january so if it is one then this is the value that we want else we want to sum up previous cell and this cell you will understand how this works once we drag this down you can see that it goes from 85 to 718 and once we hit january again it resets to the value of january and then the count goes up again how would we do that if our year starts in let's just say april in this case our formula goes like this if month of this cell is equal to four because our year starts in april which is the fourth month then the value we want is in d else sum of d5 and this value and then we can just fill this down you can see that the value goes from jan feb march in april it gets reset and then it goes again like this now how would we do all of this if we have a table format of the data the challenge with table c is because tables have the relative structural references the formula becomes slightly tricky let's observe this so with year to date from jan the formula is if my month of at the rate month is equal to one then i want registrations else i want a sum of now here is the problem our first value is registrations but the second value needs to be the value one month prior you can see that the ytd column is here because we cannot really refer to this value or anything like that this will become data headers ytd we'll have to use a different formula this is where offset comes in we could say offset of registrations go one row above so this is minus one and one column to the side one so from here it will go like this and it will point to that cell whereas from here it will go like this and point to this cell that's what offset will do and then you close all the brackets and you can see that this formula beautifully works in january it's it resets the calculation you can apply the same logic if your month begins in another starting point let's say 7 then it will go from jan to july and then in july it will reset again now just a quick note about the offset formula offset is one of the special types of formulas in excel called as a volatile formula what this means is it will recalculate every time something else changes in your workbook so you want to be careful when you are using the offset function because it can slow down the especially if you have a very large workbook. But for something small like this, the impact will be negligible. Did you enjoy that video? Do tell me in the comments how you calculate year-to-date totals using Excel formulas.
While you are at that, I highly recommend checking out the 3 Essential Date Formulas for the Finance Professionals video. It is linked on the screen there. Please feel free to check it out. I'll see you there.
Bye.