Microsoft Excel how-to video about Running Totals in Excel.
In one of my Bing Shorts, I showed two different ways to do Running Totals in Excel.
And then, an interesting comment from Carlo in Italy with a running total formula that I've never seen before.
To download the data from today, go here: Excel - Best Way For Running Totals - Episode 2590 Sample Files - MrExcel Publishing
I had to bring that formula to Excel to visualize how it was working. It was cool that it used a colon next to INDEX. But is there a simpler way?
In the video, I then compare four different Running Total Formulas. Which are easiest to enter? Which are easiest to explain? Which run the fastest? Thanks to Zack Barresse for teaching me how =SUM(Number,Text) ignore Text without giving an error. Thanks to Charles Williams for the Formula Speed whitepaper and for the Fast Excel tool to measure the speed of the formulas.
You will also catch a Nancy Faust preview of the Spinning Wheel song to entertain you while we wait for the slow versions to calculate.
Table of Contents
(0:00) Running Totals in Excel
(0:14) Bing Shorts explanation
(0:31) Formula from Carlo
(1:08) 8 Functions change when colon-adjacent
(1:40) Why not shorten INDEX?
(2:06) =Up plus =Left with two formulas
(2:45) Zack Barresse and SUM(Up,Left)
(3:15) MrExcel way with expanding range
(4:15) Judging criteria for best running total
(4:45) Charles Williams fastest Running Total
(5:07) Simple formula: less than a second
(5:24) Expanding range: 26.3 seconds
(5:44) Carlo 36.4 seconds
(5:58) Why the simple formula is faster
(6:39) =SUM(Left,Up) is winner
(7:18) Wrap-up
In one of my Bing Shorts, I showed two different ways to do Running Totals in Excel.
And then, an interesting comment from Carlo in Italy with a running total formula that I've never seen before.
To download the data from today, go here: Excel - Best Way For Running Totals - Episode 2590 Sample Files - MrExcel Publishing
I had to bring that formula to Excel to visualize how it was working. It was cool that it used a colon next to INDEX. But is there a simpler way?
In the video, I then compare four different Running Total Formulas. Which are easiest to enter? Which are easiest to explain? Which run the fastest? Thanks to Zack Barresse for teaching me how =SUM(Number,Text) ignore Text without giving an error. Thanks to Charles Williams for the Formula Speed whitepaper and for the Fast Excel tool to measure the speed of the formulas.
You will also catch a Nancy Faust preview of the Spinning Wheel song to entertain you while we wait for the slow versions to calculate.
Table of Contents
(0:00) Running Totals in Excel
(0:14) Bing Shorts explanation
(0:31) Formula from Carlo
(1:08) 8 Functions change when colon-adjacent
(1:40) Why not shorten INDEX?
(2:06) =Up plus =Left with two formulas
(2:45) Zack Barresse and SUM(Up,Left)
(3:15) MrExcel way with expanding range
(4:15) Judging criteria for best running total
(4:45) Charles Williams fastest Running Total
(5:07) Simple formula: less than a second
(5:24) Expanding range: 26.3 seconds
(5:44) Carlo 36.4 seconds
(5:58) Why the simple formula is faster
(6:39) =SUM(Left,Up) is winner
(7:18) Wrap-up
Transcript of the video:
It was the world's shortest comment, on one of the shortest videos, that led to this.
Enjoy.
The best way for running totals in Excel. So it's possible on my channel you've seen these new shorts that I'm doing, the Bing shorts.
Trying to fill about 100,000 Bing content gaps.
These videos are all easy, quick, short.
And I upload them without checking the box to Notify Subscribers.
Because I don't want to annoy you, the people are looking for something more interesting.
But yesterday I got an comment on one of these shorts.
From Carlo in Italy.
With a pretty wild running total formula.
No explanation or anything, and I was looking at that.
I was like, wow, that is crazy.
How's that working?
I've never seen an INDEX function inside of the running total.
So I came back and created a nice little run of numbers here.
And the running total.
There's the FORMULATEXT.
And as I copy this down.
And we take a look at those formulas.
What's happening here is the D5 to D13, of course, is locked.
And the 1 is locked.
The thing that's changing is what we're summing down to.
Now, I appreciate the fact that this takes advantage of the weird situation.
Where eight functions in Excel, including INDEX.
Can look at the colon and change their behavior because it's next to a colon.
So that's a deep bit of knowledge.
And kudos to Carlo for being able to know that.
And use that in the formula.
But then when I realized that the whole point of the INDEX($D$5:$D$13,1) is just to point to $D$5.
I commented back to Carlo.
I said, couldn't we just do this?
Sum of the index of D5 comma one to D5.
Like that would work.
But why go to all that hassle?
Just to point to D5 when we could just say D$5 like that.
Okay, so now that brings up the discussion of which one of these is really the best way to do a running total?
And I'm going to go back to what I call the Bill Kindergarten method.
This is what I actually used probably for 12 years while I was working in accounting.
My first formula would be equal D5.
And then after that equal up plus left.
So the number above me plus the number to the left of me.
Double-click to copy that down.
The thing that always bothered me here.
Is that I had two different formulas.
And that's just terrible having two different formulas.
The reason I had to have two different formulas?
Is had this been the number to the left of me plus the number above me.
I would get value errors all the way down.
So I just had to accept the fact it was going to be two different formulas.
But then I met Zack Barresse.
Zack has this great little fact that the SUM ignores any text cells.
So here we're taking the equal sum of the number above us and the number to the left of us.
That works in the first row.
Double-click to copy it down.
And we're good to go.
It's easy to explain.
The number above, plus the number to the left, in a SUM function.
And it will calculate, but it still didn't feel very sophisticated.
So I'm going to call this the MrExcel way.
I did this so that way I could feel like I was really good at Excel.
It was a little bit of fanciness.
And a little bit of geekiness.
So type equal sum, open paren, I'm going to type D5.
And press F4 two times to lock down just the row.
Colon, D5, close paren.
But the big problem here is if I was showing this formula to one of my coworkers.
Someone who was new to Excel.
It sure is hard to explain why we have the single-dollar-sign-five.
In fact, I really never tried to explain it here.
First, I would double click to copy down.
And then come down here to show how the D$5 said, always go back to five.
But the D13 was always the cell to the left of me.
This is what I used day to day.
And did it work?
Did I feel superior to my coworkers?
Well, I started calling myself MrExcel, so I guess so.
Carlo though takes this to a whole new level.
So as I started to think about the judging criteria here of which is the best way to do a running total.
First off, my number one thing is the same formula all the way down.
To me that's worth a thousand points.
Easy to explain from any cell, not necessarily the bottom cell, especially the top cell.
Easy to teach people to enter it.
Fast, even if 10,000 sales.
And, impress coworkers.
Another Excel MVP who shaped who I am today is Charles Williams.
I think long before I met Charles Williams, I read his great white paper on formula speed that Microsoft had published.
And in that white paper, Charles explained how this method, the “Bill Kindergarten” method, is so much faster than the sum of the expanding range.
Let's use Charles Williams’ Fast Excel to take a look at this.
So we'll do a 100,000 records.
And calculate how long it takes to add up the cell above us and the cell to the left of us.
Calc range.
That's 642.718 milliseconds.
Here's the MrExcel method.
The method I use day to day in accounting.
Calc Range.
Wow, 26,392.
And I suspect Carlo will be similar.
All right, there we go.
36,420 milliseconds.
Paraphrasing Charles Williams’ white paper on this.
It's the number of cells that are referred to by each formula.
In the Bill Kindergarten method, I'm just referring to the cell to the left of me and the cell above me.
So each of these 100,000 formulas is referring to two cells.
In the expanding range, it starts out referring to just one cell.
And then two cells.
And then three cells.
But then the problem is down here at the end, this formula is referring to 99,998 cells.
And then 99,999 cells.
And then 100,000 cells.
So while the easy method only refers to 200,000 cells to get the total.
This one is referring to 5 billion cells.
All right, so let's assign points.
Equal up plus equal left.
Two different formulas, It's out.
But the equal sum of the cell above me and the cell to the left of me.
Yeah, it gets nearly a perfect score.
It only loses one point because it won't impress your coworkers.
The expanding range?
Same formula all the way down.
But harder to explain.
Harder to teach.
And that formula penalty for just how slow it goes.
Only 501 points.
And similar for Carlo.
I'm interested to hear how you use running totals.
Particularly when you have to teach your coworkers how to use running totals.
Let me know, down in the YouTube comments below.
Well, hey, I want to thank Carlo for that very intriguing formula.
And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Enjoy.
The best way for running totals in Excel. So it's possible on my channel you've seen these new shorts that I'm doing, the Bing shorts.
Trying to fill about 100,000 Bing content gaps.
These videos are all easy, quick, short.
And I upload them without checking the box to Notify Subscribers.
Because I don't want to annoy you, the people are looking for something more interesting.
But yesterday I got an comment on one of these shorts.
From Carlo in Italy.
With a pretty wild running total formula.
No explanation or anything, and I was looking at that.
I was like, wow, that is crazy.
How's that working?
I've never seen an INDEX function inside of the running total.
So I came back and created a nice little run of numbers here.
And the running total.
There's the FORMULATEXT.
And as I copy this down.
And we take a look at those formulas.
What's happening here is the D5 to D13, of course, is locked.
And the 1 is locked.
The thing that's changing is what we're summing down to.
Now, I appreciate the fact that this takes advantage of the weird situation.
Where eight functions in Excel, including INDEX.
Can look at the colon and change their behavior because it's next to a colon.
So that's a deep bit of knowledge.
And kudos to Carlo for being able to know that.
And use that in the formula.
But then when I realized that the whole point of the INDEX($D$5:$D$13,1) is just to point to $D$5.
I commented back to Carlo.
I said, couldn't we just do this?
Sum of the index of D5 comma one to D5.
Like that would work.
But why go to all that hassle?
Just to point to D5 when we could just say D$5 like that.
Okay, so now that brings up the discussion of which one of these is really the best way to do a running total?
And I'm going to go back to what I call the Bill Kindergarten method.
This is what I actually used probably for 12 years while I was working in accounting.
My first formula would be equal D5.
And then after that equal up plus left.
So the number above me plus the number to the left of me.
Double-click to copy that down.
The thing that always bothered me here.
Is that I had two different formulas.
And that's just terrible having two different formulas.
The reason I had to have two different formulas?
Is had this been the number to the left of me plus the number above me.
I would get value errors all the way down.
So I just had to accept the fact it was going to be two different formulas.
But then I met Zack Barresse.
Zack has this great little fact that the SUM ignores any text cells.
So here we're taking the equal sum of the number above us and the number to the left of us.
That works in the first row.
Double-click to copy it down.
And we're good to go.
It's easy to explain.
The number above, plus the number to the left, in a SUM function.
And it will calculate, but it still didn't feel very sophisticated.
So I'm going to call this the MrExcel way.
I did this so that way I could feel like I was really good at Excel.
It was a little bit of fanciness.
And a little bit of geekiness.
So type equal sum, open paren, I'm going to type D5.
And press F4 two times to lock down just the row.
Colon, D5, close paren.
But the big problem here is if I was showing this formula to one of my coworkers.
Someone who was new to Excel.
It sure is hard to explain why we have the single-dollar-sign-five.
In fact, I really never tried to explain it here.
First, I would double click to copy down.
And then come down here to show how the D$5 said, always go back to five.
But the D13 was always the cell to the left of me.
This is what I used day to day.
And did it work?
Did I feel superior to my coworkers?
Well, I started calling myself MrExcel, so I guess so.
Carlo though takes this to a whole new level.
So as I started to think about the judging criteria here of which is the best way to do a running total.
First off, my number one thing is the same formula all the way down.
To me that's worth a thousand points.
Easy to explain from any cell, not necessarily the bottom cell, especially the top cell.
Easy to teach people to enter it.
Fast, even if 10,000 sales.
And, impress coworkers.
Another Excel MVP who shaped who I am today is Charles Williams.
I think long before I met Charles Williams, I read his great white paper on formula speed that Microsoft had published.
And in that white paper, Charles explained how this method, the “Bill Kindergarten” method, is so much faster than the sum of the expanding range.
Let's use Charles Williams’ Fast Excel to take a look at this.
So we'll do a 100,000 records.
And calculate how long it takes to add up the cell above us and the cell to the left of us.
Calc range.
That's 642.718 milliseconds.
Here's the MrExcel method.
The method I use day to day in accounting.
Calc Range.
Wow, 26,392.
And I suspect Carlo will be similar.
All right, there we go.
36,420 milliseconds.
Paraphrasing Charles Williams’ white paper on this.
It's the number of cells that are referred to by each formula.
In the Bill Kindergarten method, I'm just referring to the cell to the left of me and the cell above me.
So each of these 100,000 formulas is referring to two cells.
In the expanding range, it starts out referring to just one cell.
And then two cells.
And then three cells.
But then the problem is down here at the end, this formula is referring to 99,998 cells.
And then 99,999 cells.
And then 100,000 cells.
So while the easy method only refers to 200,000 cells to get the total.
This one is referring to 5 billion cells.
All right, so let's assign points.
Equal up plus equal left.
Two different formulas, It's out.
But the equal sum of the cell above me and the cell to the left of me.
Yeah, it gets nearly a perfect score.
It only loses one point because it won't impress your coworkers.
The expanding range?
Same formula all the way down.
But harder to explain.
Harder to teach.
And that formula penalty for just how slow it goes.
Only 501 points.
And similar for Carlo.
I'm interested to hear how you use running totals.
Particularly when you have to teach your coworkers how to use running totals.
Let me know, down in the YouTube comments below.
Well, hey, I want to thank Carlo for that very intriguing formula.
And I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
