In our second look at Formula Recalc, a 750x improvement in formula recalc times by using some helper cells. Episode 1014 shows you how.
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 am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this well.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, alright! welcome back to the MrExcel netcast.
I am Bill Jelen.
The spring Seminar season is just about it, where we have one more big seminar out in Denver.
That's the national IMA conference, So for the most part of I'm not going to be getting Excel questions every week as I travel around doing the seminars.
If you've sent your question into the podcast before or if you have a question that you'd like to see on the podcast, please by all means send it to me.
This is a great time.
I need some questions for the podcasts.
Either drop an email at bill@MrExcel.com.
or if you want to be on the podcast, leave your question as a voicemail.
8665810221 Now, introduce yourself.
Hey, This is Mary from Chicago And answering to your question well, I will be able to help you out.
Another example here of formula recount times.
We talked about this on Monday at 1011, where we designed a better formula.
This time I want to talk about something called helper cells.
Now, I have 11,000 rows here the eleven thousand rows are each calculating Revenue as a percentage of the total revenue for that product.
So, I thought this formula out here is pretty cool to be honest.
Take the Revenue in this row divided by the SUMIF.
Look's through all the rows of column B.
See if it's equal to XYZ.
If it is, add of the corresponding cell in column E.
That nice little SUMIF function there but think about this SUMIF function.
That means on every single row, It's going to go evaluate 11823 other cells.
So, we just have a huge...
equal 11,823 raised to the second power.
139 Million references, they have to be calculated.
In order to calculate this, Now, I'm going to use the same macro that I used before this macro.
Right now, it's out doing 139 million various references, and we'll see how long it takes.
It's been running somewhere in the fifteen to twenty second range.
So waiting for that, let me talk about the problem with this is this entire line.
So, that means that once for XYZ on row 2, it's going to come up again and again and again and calculate that same number over and over.
Alright so check that out.
31 seconds to calculate that range.
Now, I'm going to go on to another worksheet, where I've changed things a little bit.
Over here I put the really time intensive calculation.
These three cells...
Three cells out here that calculate the total for XYZ, the total for DEF and the total for ABC.
Put those out to the side those are called helper cells.
So rather than having to calculate 11,000 times, the SUMIF it only calculates at once and then here in the actual formula, we're doing E2 divided by the VLOOKUP.
Now, the VLOOKUP is going to be fast.
You'll notice that I'm using not the comma false version exact match.
The LOOKUP range is Sorted There's only three items in the lookup range, so it's going to run lightning-fast.
Now, remember the very first time.
The previous example to calculate 31 seconds.
And now we're going to calculate this one.
Check that out less than...
well 0.01.
Almost 0.02 seconds, 1/50th of a second.
Dramatically faster by taking those three values and putting them out and a helper cell.
Even though we had to do 11,000 VLOOKUPs, the VLOOKUPs were lighting fast because that was a small table.
So, there's an example where we went from 31 seconds to zero point.
Let's call it 02 seconds.
Just a dramatic change in count speed by thinking about what is in this calculation that can be moved out to a secondary cell and then refer to a secondary cell.
So, that way the whole thing does not have to be calculated, again and again and again.
Great example of Recount speed.
If you like this one, make sure to watch episode 1011 back on Monday and check out the white paper mentioned in that episode because it goes through many more examples like this.
Why hey I want to thank you for stopping by.
Again if you have any questions, feel free to drop me a note and we'll get to you on a future podcast.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Basically, we start out with massive amount of data.
We say how we're going to analyze this well.
Let’s fire up a pivot table.
Let’s see if we can solve this problem.
Hey, alright! welcome back to the MrExcel netcast.
I am Bill Jelen.
The spring Seminar season is just about it, where we have one more big seminar out in Denver.
That's the national IMA conference, So for the most part of I'm not going to be getting Excel questions every week as I travel around doing the seminars.
If you've sent your question into the podcast before or if you have a question that you'd like to see on the podcast, please by all means send it to me.
This is a great time.
I need some questions for the podcasts.
Either drop an email at bill@MrExcel.com.
or if you want to be on the podcast, leave your question as a voicemail.
8665810221 Now, introduce yourself.
Hey, This is Mary from Chicago And answering to your question well, I will be able to help you out.
Another example here of formula recount times.
We talked about this on Monday at 1011, where we designed a better formula.
This time I want to talk about something called helper cells.
Now, I have 11,000 rows here the eleven thousand rows are each calculating Revenue as a percentage of the total revenue for that product.
So, I thought this formula out here is pretty cool to be honest.
Take the Revenue in this row divided by the SUMIF.
Look's through all the rows of column B.
See if it's equal to XYZ.
If it is, add of the corresponding cell in column E.
That nice little SUMIF function there but think about this SUMIF function.
That means on every single row, It's going to go evaluate 11823 other cells.
So, we just have a huge...
equal 11,823 raised to the second power.
139 Million references, they have to be calculated.
In order to calculate this, Now, I'm going to use the same macro that I used before this macro.
Right now, it's out doing 139 million various references, and we'll see how long it takes.
It's been running somewhere in the fifteen to twenty second range.
So waiting for that, let me talk about the problem with this is this entire line.
So, that means that once for XYZ on row 2, it's going to come up again and again and again and calculate that same number over and over.
Alright so check that out.
31 seconds to calculate that range.
Now, I'm going to go on to another worksheet, where I've changed things a little bit.
Over here I put the really time intensive calculation.
These three cells...
Three cells out here that calculate the total for XYZ, the total for DEF and the total for ABC.
Put those out to the side those are called helper cells.
So rather than having to calculate 11,000 times, the SUMIF it only calculates at once and then here in the actual formula, we're doing E2 divided by the VLOOKUP.
Now, the VLOOKUP is going to be fast.
You'll notice that I'm using not the comma false version exact match.
The LOOKUP range is Sorted There's only three items in the lookup range, so it's going to run lightning-fast.
Now, remember the very first time.
The previous example to calculate 31 seconds.
And now we're going to calculate this one.
Check that out less than...
well 0.01.
Almost 0.02 seconds, 1/50th of a second.
Dramatically faster by taking those three values and putting them out and a helper cell.
Even though we had to do 11,000 VLOOKUPs, the VLOOKUPs were lighting fast because that was a small table.
So, there's an example where we went from 31 seconds to zero point.
Let's call it 02 seconds.
Just a dramatic change in count speed by thinking about what is in this calculation that can be moved out to a secondary cell and then refer to a secondary cell.
So, that way the whole thing does not have to be calculated, again and again and again.
Great example of Recount speed.
If you like this one, make sure to watch episode 1011 back on Monday and check out the white paper mentioned in that episode because it goes through many more examples like this.
Why hey I want to thank you for stopping by.
Again if you have any questions, feel free to drop me a note and we'll get to you on a future podcast.
We'll see you next time for another netcast from MrExcel.
