Hey, welcome back to the Mr. Excel Net Cast. I'm Bill Jelen. Amazing, we start out with massive amounts of data as to how are we going to analyze this. Well, let’s fire up the pivot table and see if we can solve this problem.
Welcome back to the Mr. Excel Net Cast, I'm Bill Jelen. I want to thank Mike Gel Girvin. Mike gave me a pointer to this cool white paper color, Improving Performance in Excel 2007 by Charles Williams. Here we’ll 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 calculating speed and I just want to illustrate one amazing thing I saw here. We need out a running total to this columns, 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 of that wheel handle.
I was never big fan of that because you have there one formula and then a second formula and so, I've kind of geek out with the “better way to do it”. The dollar sign too to be to in the sum and that automatically expands and we get the exact the same answers as we copy it down, alright, simple enough. But in the white paper, it started to talk about the calculation speed in which way it 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 this is coming here at 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 at the way down to this that has to look at 8,479 cells. And so, if you add all this up 8,479 plus 8,478 plus 8,477, all the way back up to the one that look at just one cell. The recap time is dramatically longer.
Here’s an example, I'm going to choose this one. I'm might going to use a little macro it was in Charles’ white paper. I'm going to press control S on my screen and so we count the number of micro seconds to calculate that range, so its 1.1969 seconds. Now, I'm going to come over and select the exact same size range using the simpler format the one that I use to think wasn’t sophisticated, press Control S. Method two calculates instead of 1.19 seconds in .018 seconds. Dramatically faster, its by an order of a 100 times faster this calculating.
So, interesting when you start to get large cells, you know, what appears to be the “best formula may not in terms of calculation speed”. So this is a great white paper, if you’re fascinating and stuffs like this Improving Performance in Excel 2007 just all kind of things like. So, shout out to Charles Williams, great, great white paper. Check it out again at the URL that I gave earlier. I want to thank you for stopping by. We’ll see next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services