Running Totals

Learn Excel for MrExcel Podcast, Episode 2004 -- Running Totals

I'll be podcasting this entire book. Click that I on the top right hand corner to subscribe.

Hey welcome back to the mystic cell netcast. I'm Bill Jelen. Now this topic in the book, I was contributed by my friend Zach Parise. Talk about Excel tables, Zach is the is the world's expert on Excel tables. He's written a book about Excel tables, but first let's talk about running totals not in tables.

So when I think about running totals, there's three different ways to do the running totals, and the way that I always started out with is in the first row you just say, bring the value over. So equal whatever's to the left of me. Alright so this format here is just =B2. These are all formula text here on the right-hand corner so you're seeing what we're using, and then from there on down, it's a simple little formula of equal the previous value, plus the current value right and copy that down, but you know now, we have this problem that it required two different formulas and you know in a perfect situation you have the exact same formula all the way down, and the reason we have to have a different formula there in the first row is that when you try and add equal 7 plus the word total it's a value error, but the cool worker out here, is to not just use left plus up, but to use =(SUM) of the previous value plus the quantity in this row, and see some is far enough to ignore texts. Right so that allows the same formula. all the way down.

Alright so that was when I was starting out using Excel, I was using that and then I discovered the expanding range, the expanding range says we're going to do L$2:L2 and what happens is this is always starting at row 2, but then it's going down to the current row. So when you look at how this works when it gets copied, we always started row 2, but we go down to the current row and this became my favorite method. I was like, oh, this is so much more sophisticated and when we go into Excel Options, go to the Formulas Tab and choose R1C1 in Reference Style. Alright see,  R1C1, all of these formulas are exactly the same all the way down. I'm don't know if you understand R1C1, it's just good to know that we have identical R1C1 formulas all the way down.

Let's go back. So this method over here is the method that I liked, until until Charles Williams, an Excel MBP from England, who has an amazing paper on formula speed, Excel formula speed, completely debunked this method. This method, let's say you have 10,000 rows this, every single formula is looking at two references. So you're looking at 20,000 references, but this one, this is looking at two, this is looking at three, this is looking at four, this is looking at five and the last one is looking at 10,000 references, and it's horribly slower and so I stopped using this method.

Then I go on to read Zack in Kevin Jones's book about Excel tables and I discover yet another problem with this method. So one of the useful features the tables offers, is 'automatic formatting and formula maintenances rows are added, removed, sorted and filtered'. Alright that's a quote from his book. And to add a row to a table you just go to the very last cell on the table and press tab. So everything is working here. We're down to 70 right that's awesome and then A104 and I'll put in a 100 here. Alright, so that 70 should change to 170 and it does, but this 70 shouldn't have changed at all. Alright 68 + 2 is not a 170. I'll do it again. A 104 and put another hundred in the last one is right. These two are not right. Alright, so we have some weird situation that if you're using this formula and you convert over to table you start adding rows, the running total is not going to work. How bad is that?

Alright, so Zack offers two work arounds and both of them require a little bit of knowledge, of how structure references work. We're just going to have a new column out here and if I wanted to do quantity, equal quantity, right, so that =[@Qty] says quantity in this row. Oh cool, well there's another kind of reference where we use the Qty without the @. Check this out. So =SUM(INDEX([Qty),1:[@Qty]) means all of the quantities and we're going to say that we want to SUM from the first quantity, so (INDEX([Qty),1 says the first value here, down to the current row quantity, and this is using a really special version of index, when index is followed by a colon, it actually changes to a cell reference. Alright so, this workaround is unfortunately violating the Charles Williams rule of, we're going to have to look at every single reference, and so when you get 10,000 rows of this is going to go really, really slow.

Zach has another workaround that doesn't violate the Charles Williams problem, but it's using the dreaded OFFSET. OFFSET is a volatile function so every time that you calculate something, OFFSET is going to recalculate and everything down line from the OFFSET's going to recalculate. It's just a great way to completely, completely screw up your your formulas, and what this is doing, it's saying, we're taking the total from this row, going up one row, over zero columns and so what that's doing is saying: grab the total from the previous row and then we're adding to it the quantity from this row. Alright, so, now it's all looking at two references each time, but unfortunately the OFFSET is introducing volatile functions.

Well, there you have it, more than you ever wanted to know about Running Totals. I guess my final opinion here is to use this method, because it only looks it two. Same formula all the way down and your structured table references will work.

For this exploration and 39 other really good tips, check out this book MrExcel XL, the 40 greatest Excel tips of all time.

Recap for this episode we talked about three ways to do running totals. The first method has a different formula, row 2, than all the other rows. It's equal left in row 2 and then equal left plus up in rows 3 through N, but if you try and just use that same formula, equal left plus up, all the way down, how you're going to get a #Value Error. So =SUM(Up,Left), which is previous total, plus this roadmap, that works great, no Value Errors and then the expanding range which I use to love. They're cool, but until I read Charles Williams white paper on Excel form of speed. Then I started to hate these expanding references. It also has a problem when you use CTRL T and add new rows. Excel can't figure out how to expand that formula, how to add new rows. I love this tip go to the very last cell in the table and press Tab, that will add a new row and then we talked about some structured referencing, where we're using quantity in this row and then all quantities. =SUM(OFFSET([@Total],-1,00,[@Qty]).

Okay, I want thank Zach for contributing that tip. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.