Mike Girven pointed me to an excellent whitepaper by Charles Williams about calculation speed. In Episode 1011, an example contrasting different ways of entering running totals. One calculates 100 times faster than the other!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're going to analyze this.
Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I want to thank Mike Girvin, Mike gave me a pointer to this cool white paper called improving performance in Excel 2007 by Charles Williams.
Here will zoom in on the URL: msdn.microsoft.com/en-us/library/aa730921.aspx.
Fascinating 50 page document that goes through a whole lot of issues, about calculation speed and I just want to illustrate one amazing thing I saw here. We need to add a running total to this column so there's 10,000 rows.
How would you add the running total?
Well, I've seen a lot of people and initially I would do this.
I would always put in the first formula and then use plus the previous number plus the current number and send that down, double click the fill handle.
I was never a big fan of that because you have to enter one formula, and then a second formula and so I've kind of geeked out with the quote unquote.
Better way to do it =SUM(B$2:B2).
And that automatically expands, and we get the exact same answers as we copy it down. All right simple enough, but in the white paper it started to talk about the calculation speed and which way was a faster way to go and it's very intuitive exactly what's happening here, each cell is adding up two cells.
So, if we have 10,000 numbers, basically you're looking at 20,000 references.
No, hassle at all.
But you know, let's just come here this cell has to look at six cells or five cells this has to look at five cells and this one has to look at six cells and this one has to look at seven cells all the way down to this one that has to look at 8479 cells.
And so, if you add all this up, 8479+8478+8477 all the way back up to the one that looks at just one cell the recap time is dramatically longer.
Here's an example I'm gonna choose this one. I'm gonna use a little macro.
It was in Charles white paper, I'm going to press control+S on my screen.
It's going to count the number of microseconds to calculate that range. So, it's 1.1969 seconds.
Now, I want to come over and select the exact same size range using the simpler formula the one that I used to think was a sophisticated.
Press control+S, method 2 calculates instead of one point 19 seconds in point 018 seconds.
Dramatically, faster it's by an order of a hundred times faster that it's calculating.
So, interesting when you start to get large cells.
You know what appears to be the quote-unquote best formula may not I in terms of calculation speed.
So, this is a great white paper if you're fascinating and stuff like this improving performance in Excel 2007, just all kinds of things like that so shout-out to Charles Williams.
Great, great white paper check it out again at the URL I gave earlier Wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're going to analyze this.
Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
I want to thank Mike Girvin, Mike gave me a pointer to this cool white paper called improving performance in Excel 2007 by Charles Williams.
Here will zoom in on the URL: msdn.microsoft.com/en-us/library/aa730921.aspx.
Fascinating 50 page document that goes through a whole lot of issues, about calculation speed and I just want to illustrate one amazing thing I saw here. We need to add a running total to this column so there's 10,000 rows.
How would you add the running total?
Well, I've seen a lot of people and initially I would do this.
I would always put in the first formula and then use plus the previous number plus the current number and send that down, double click the fill handle.
I was never a big fan of that because you have to enter one formula, and then a second formula and so I've kind of geeked out with the quote unquote.
Better way to do it =SUM(B$2:B2).
And that automatically expands, and we get the exact same answers as we copy it down. All right simple enough, but in the white paper it started to talk about the calculation speed and which way was a faster way to go and it's very intuitive exactly what's happening here, each cell is adding up two cells.
So, if we have 10,000 numbers, basically you're looking at 20,000 references.
No, hassle at all.
But you know, let's just come here this cell has to look at six cells or five cells this has to look at five cells and this one has to look at six cells and this one has to look at seven cells all the way down to this one that has to look at 8479 cells.
And so, if you add all this up, 8479+8478+8477 all the way back up to the one that looks at just one cell the recap time is dramatically longer.
Here's an example I'm gonna choose this one. I'm gonna use a little macro.
It was in Charles white paper, I'm going to press control+S on my screen.
It's going to count the number of microseconds to calculate that range. So, it's 1.1969 seconds.
Now, I want to come over and select the exact same size range using the simpler formula the one that I used to think was a sophisticated.
Press control+S, method 2 calculates instead of one point 19 seconds in point 018 seconds.
Dramatically, faster it's by an order of a hundred times faster that it's calculating.
So, interesting when you start to get large cells.
You know what appears to be the quote-unquote best formula may not I in terms of calculation speed.
So, this is a great white paper if you're fascinating and stuff like this improving performance in Excel 2007, just all kinds of things like that so shout-out to Charles Williams.
Great, great white paper check it out again at the URL I gave earlier Wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
