Excel Dynamic YTD Calculations: OFFSET, SUMPRODUCT & SUM

in this lecture I want to show you how you can use formula combinations so in this specific case we're going to be using some some products and offset to get the result that you want for the task at hand in this case we've been given some data it's monthly data on volumes and prices our months go through a few years I have the data for the first year here then we start off again from January for the next year and then for the third year I've just put in the January value here what we need to do is at each point in time to show the year-to-date number and not just for volume but also for price price is going to be a bit trickier because you can't just add the prices here you need to always calculate average or today price by multiplying volume times price and adding the value of that to the next month's volume times price and dividing it by your year-to-date volumes it's going to require some more steps but first off we're going to start simple and concentrate on getting the year-to-date volume in a dynamic way and by dynamic way I mean that you type the formula in once here and then you drag it all the way to the end of this report you can see probably where the problem is and that's when you get here here I've written the not optimal method and that's basically if you use the sum function of this with itself and we're going to fix the first solve our friends we're going to drag it down so you can see that once we come to January obviously we don't want the year-to-date of adding year to date December to January we need to start and revise the formula again so we have to make sure that we do this and then drag this across the ones we get to here again we need to be careful that we need to revise this formula can that is prone to mistakes because if someone comes and they think okay you have a very dynamic formula here they drag this all the way to the end they're going to overwrite these correct formulas with wrong data how do we avoid this from happening by making this dynamic what part of this formula do we need to replace with something dynamic our end is here the start in this case we fix to be 7 if somehow we can fix that this be 7 changes to a dynamic range that every time we go to the next month it's actually expanding a range then we're on the right track what we need to make sure of is that we are taking into account if we come back to January that our range is again just restricted to one but when it goes to 2 - February to the second month is taking the data from the two columns from the two cells and when it goes to the third month March it's taking the data of these three cells one formula can help us in this one formula that always helps with dynamic ranges is the offset formula so instead of using b7 here I can try to put in the offset formula there I'm just going to copy this here and we're going to replace this with offset first we need our starting point now our starting point can be the same column that we're always on and we don't need to fix it because we can make our starting point move with us what we want for rows is zero right we don't want to move any rows down we don't want to move any columns all we want to do is that the width of our data the width of the columns is more than one if we are in a month that's greater than January so our height is also one we just need the data of this one row we don't need this or this above it it stays one what we want to be dynamic is the width and the width is always going backwards right if we're in February we want Jan and fab over in March we want Jan Feb and March so it's getting bigger but backwards bigger not future bigger so we need to work with - numbers here we want it to be in a way that if I drag this formula up that this is going to be 1 and here is going to be minus 2 here is going to be minus 3 - 4 - 5 and so on for the first one it does it wouldn't really matter right if I put one here and close the bracket here and put that I get a hundred what happens if I put minus one I get a hundred it's a 1 and minus 1 are the same can I put a 0 here no the width of a cell can never be zero it has to be at least one cell but minus one doesn't provide any errors either it's fine if I put pull this here I would need this not to be minus one but to become minus two right and then I get the same values if I pull this here I need this to become minus three okay and then I get the same values so how can I do that in a dynamic way that first I have minus one then I have minus two and minus 3 well one way is that you can use helper cells and put one two three here and then reference this cell here but we want to avoid using helper cells as much as possible so let's say we don't have space for that what you can do is to use a formula that gives us minus 1 then minus 2 and minus 3 now here I have the months I have them formatted as dates I'm just showing the first three characters I could if I press ctrl 1 and just have a look at the custom formatting of this I put mmm which means to show me the three first characters of the month if I add another M I'm going to show January for this here the important thing is that they are dates in the background and one formula that can give us the month number is the month formula close brackets so the month formula would give one here would give two here three here and I'm put minus month so then I get - two - three here does that work when I come to January here yes it does why because my range contracts again my range becomes just minus 1 and then here again it becomes minus 2 and then it's these two values so it's difficult to see what your cells are showing because when you click on the formula is only showing February but it's not only using February it's using January and February so we might see it better if I go to formulas and evaluate formula and we can see here we can debug our formula of n7 207 okay so here it's more visible by clicking in here you can't really see so do check evaluate formula if you're trying to debug your formulas here so that's how we can get the volume part to become dynamic now let's have a look at the price if we do it the manual wave is it a non optimal method we're going to use a helper cell to get volume times price in each month so I'm just going to do this times this and drag it all the way across now what we could write is this is would be the sum of this to this sum is similar what we did for volume divided by our year-to-date volume here and we have to fix the first cell reference we're going to put this across but is that right no because I run into a problem here I need to revise my formula again just like I did before so I need to make sure this starts again from here I copy this to here and then do the same for this one which is again prone to mistakes what we want to do in the dynamic version of it is to not just make this part dynamic that we don't run into this problem here but we want to avoid having a helper cell here and this is where we can use the sum product formula so if we write this without now the offset function so let's just try to solve the problem of not using this row here we're going to do some product I'm going to be multiplying the volume by the price but I want my range to expand when I pull my formula this way so it's going to be this with itself multiplied by this with itself and again like we did before we're going to fix the first cell reference so that's going to give us the year-to-date version of this so if I pull this here you can see this is 839 it's going to be the sum of these three if I divide this by my new dynamic year-to-date volume formula I get that price so I can copy it all the way here it's fine I don't need to use this helper row but here again I have that problem which I can fix right now so just like we did before we need to use the offset function for the first cell reference for the one that we're actually fixing because we want that to be dynamic so we're going to use offset here for volume we can start here we want to move zero rows zero columns height is one and our width again is going to be in this case minus one that we want minus two minus 3 and so on so we're going to use - month of this to get minus one out of it okay let's just leave the formula make sure everything is fine I have to be in sync obviously so if I'm putting the offset here of volume I need to multiply it by the same price in that month so my ranges need to be the same I'm going to change this one for the price also to the offset function we're going to start here we're going to move zero zero one and the width again is - month this cell close brackets okay so now let's see if that's solved and that looks good that looks good as well so that's how you can use formula combinations to avoid using helper cells but also to avoid having to change your formula in the middle of your data set which can be prone to mistakes I hope you found this lecture useful and I'll be interested to see your method of handling this

Channel: Leila Gharani

Views: 128,085

Rating: 4.9501486 out of 5

Keywords: excel year to date, calculate year to date in excel, Dynamic YTD, sum until year end, SUMPRODUCT, SUMPRODUCT Function, OFFSET, YTD Calculation, XelplusVis, EXCEL complex formulas, Advanced Excel, Master Excel, Learn Excel Formulas, Excel formulas, Excel Tips, Excel Tips and Tricks, Best Excel Online Course, Excel Advanced formulas, Excel for analysts, Microsoft Excel expert, Leila Gharani, SUMPRODUCT Formula, sum month to date

Id: fkv3o4x_KHU

Length: 13min 14sec (794 seconds)

Published: Wed Nov 30 2016