Abhijeet took INDIRECT to the extreme - 1200 formulas, each looking at 5600 records and the recalc time is taking too long. Two ways to build those formulas without INDIRECT.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Podcast Episode 1824: Too Many INDIRECT Slows Calc Time.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, this is a timely episode to talk about too many INDIRECTs.
So, in episode 1822 and 1823 this week, I showed you cool ways that we could use INDIRECT but I was only doing, you know, maybe twelve rows by four columns.
It wasn't a lot of those, thirty-six, and today I have a spreadsheet sent in by Abhijeet and Abhijeet has a large database here.
So, back here on SHEET1, we have 5600 rows by 80 columns and Abhijeet created named ranges, so he selected the whole range, FORMULAS, NAME, CREATE FROM SELECTION, so that way each heading corresponds to all of the cells for that column.
So, for example, if I would come here and choose DILL, it's going to select everything in column Y all the way down to row 5600, and then Abhijeet had some AGE ranges.
So, here's the AGE LOWER, AGE UPPER.
TOTAL was simple enough -- that was COUNTIFS -- but then he needed to see how many people between these ages had various symptoms.
Now, I’ve replaced the symptoms with just food names because it's confidential data but this is a lot of formulas here.
There are -- let's see, CONTROL+SHIFT+RIGHTARROW -- forty different columns of formulas and so you would have to enter forty different formulas all the way across.
So, what Abhijeet did -- and it's clever -- is he used the INDIRECT to go up and grab the name from F2.
So, first thing he does is check to see if the age is greater than or equal to B3; second thing, see if the age is less than or equal to the ending age in C3; and then check to see if the heading, the range name, up in F2 is not equal to blank.
Alright.
Now, the problem that we have here is I'm just going to come out to a cell that no one is pointing to.
I'm going to type the number 1 and I'll press ENTER and it's still calculating.
There. It finished. Alright. Now, how long was that?
Well, here, I actually have a macro that’ll tell us how long that was, thanks to Charles Williams. I took his code.
CONTROL+SHIFT+C, we just type the number 1 in, it takes 1.53384 seconds to calculate this spreadsheet.
Now, is that bad?
Well, yeah, it is bad especially if you're doing a lot of data entry, and Abhijeet says, hey.
I'm about to make this much more complicated by checking the city name or something like that.
I’m adding more features in so I'm going to exponentially grow from 1200 formulas to even more formulas and it's just going to get worse.
So, the problem that we have, and when I enter a number out here, none of the cells in this range are referring to this number, but because Excel doesn't know what INDIRECT it’s pointing to, it has to go through and recalculate every single formula, all 1200 INDIRECT.
All 1200 formulas that contain INDIRECT have to get recalculated which is going to take a lot of time.
So, 1.53 or something like that, 1.53 seconds was what we had before and I said to Abhijeet, I said, this is going to be a horrible set of steps.
You're not going to believe this but it's going to actually make your life easier.
I want you to come to the very first formula and we're going edit that formula and we're going to make it into text.
So I'm going to say, it's going to be =, “, and then the ^, and, at the very end, I'm going to add a ”. Alright.
So, what I've done now, in addition to adding the ^ character in, is I've created a formula that is the text of the original formula.
Alright, but now we have to do a few more things.
Anytime that there was a quotation mark in this form, then we have to replace it with “” because that's how you put a quote inside of quotes, so “”. Even if it looks silly out here, when we have this…so you need a “ there, “ there.
Alright, so, you go through it and double up all of the quotes inside.
Alright.
That's step two.
Step one, add this little bit at the beginning; I guess step two is add the quotation at the end; and then, step 3, anywhere in the middle where there's a “, put an extra “, okay?
And then, when we get to the point where they're putting &B3, right there after the [ unintelligible n sign. I’m going to close the “, put an & so that will put whatever is in B3, and then after B3 put an & and a “, and then -- the same thing out here -- after this ampersand, close the “, an & joins whatever's in C3, and then an & and a “. When we get to the INDIRECT -- now this is the fun part -- so, we actually have that , there, do a “&. We can get rid of the word INDIRECT in the (, leave E$2 there, and then another & and a “ in place of the ). Alright.
Press ENTER.
Now, this formula is a very long formula.
If we would take a look, it is replicating the original formula but it's putting in the range name right there.
Okay.
So, undo.
I'm going to take that formula now.
I’m going to copy the formula, CONTROL+C, CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW, CONTROL+V, and copy that formula throughout, and these next two steps are incredibly important or the whole thing won't work.
We have to copy all of those formulas and PASTE as VALUES.
Alright.
So, what we've done now is we've used a formula to build the formula and the starting ^= is a common trick to make sure that these formulas are not evaluated right now, but now that we've converted the text back to values, CONTROL+H, and change your recurrence of ^= to just an = sign, REPLACE ALL, and Excel goes through, makes those replacements, and we have the calculation.
Boy, that was an insane amount of work, right, but here is the reason why we did that.
It was 1.53 seconds to calculate before.
Now, run the macro -- CONTROL+SHIFT+C -- .0032 seconds, .0032 seconds.
That is a tremendous speed improvement there.
So, it's currently calculating in two-tenths of a percent of the time that it calculated before.
Alright.
Now, I sent this back to Abhijeet and Abhijeet said, well, that's great but now you've essentially hard-coded the ages, so we're saying > or = 0, < or = to 3, and while I can live with that, that is not the best way to go at all, alright?
So, that was method number one which is all formulas and requires no VBA.
I'm going to close this workbook, FILE, CLOSE, DON’T SAVE, and reopen the workbook.
Okay, so now we're back to the version that takes 1.53 or 1.61 seconds to calculate, and I'm going to switch over to VBA -- ALT+F11 -- where I have this code that I wrote.
So, the idea is going to be I'm going to select all of the cells that contain the INDIRECT formula, we're going to loop through each cell in the selection, get the old formula, figure out where the INDIRECT starts.
So, we take the left part of the old formula to get the start of the new formula and then we go out nine characters because INDIRECT + the ( is 9 characters, and, from there, we want to figure out what's inside of the INDIRECT.
Alright, so, the goal is to start looking at characters until we encounter the ).
Now, in this case, it's just going to be really simple.
It's going to be something like C$2 or E$2 or F$2, but let's say you have something more complex inside the INDIRECT.
You would collect all of that and then pass that to the range function.
So, the range…I've stored my range.
This is going to be C$2, D$2 -- in this case, really simple -- pass that to the range and see what the current value of that is.
So, in essence, this line right here is doing the INDIRECT.
We've then stored that and a variable so that we have the original left part of the formula, the new text, and then everything after the INDIRECT ). Once we have all that, write that back to the cell formula, and then the EXIT.FOR to go back up to the next cell. It exits this for i=1.
What we're looking for the stuff inside the INDIRECT.
Alright, so, just a quick little bit of code here.
Let's switch back to Excel, ALT+F8, CHANGEINDIRECT, click RUN.
Alright.
So, now the INDIRECT is no longer there.
I've used the VBA to write the formulas, and let's run Charles William’s macro here to see how long it takes.
CONTROL+SHIFT+C. Even faster now -- .0024 seconds -- than using the formula method, and the beautiful thing is, if I would change something here, the formulas continue to update throughout.
So using VBA…well, both methods were horrendous but either method is easier than writing forty different formulas going across.
Alright, there you go.
Okay, so, twice this week I've used INDIRECT and it worked out great because we only had thirty-six cells that were using INDIRECT.
When you come up with 1200 cells using INDIRECT and each one's looking at 5000 rows, it can get really slow.
So, I want to thank Abhijeet for this great question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Podcast Episode 1824: Too Many INDIRECT Slows Calc Time.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, this is a timely episode to talk about too many INDIRECTs.
So, in episode 1822 and 1823 this week, I showed you cool ways that we could use INDIRECT but I was only doing, you know, maybe twelve rows by four columns.
It wasn't a lot of those, thirty-six, and today I have a spreadsheet sent in by Abhijeet and Abhijeet has a large database here.
So, back here on SHEET1, we have 5600 rows by 80 columns and Abhijeet created named ranges, so he selected the whole range, FORMULAS, NAME, CREATE FROM SELECTION, so that way each heading corresponds to all of the cells for that column.
So, for example, if I would come here and choose DILL, it's going to select everything in column Y all the way down to row 5600, and then Abhijeet had some AGE ranges.
So, here's the AGE LOWER, AGE UPPER.
TOTAL was simple enough -- that was COUNTIFS -- but then he needed to see how many people between these ages had various symptoms.
Now, I’ve replaced the symptoms with just food names because it's confidential data but this is a lot of formulas here.
There are -- let's see, CONTROL+SHIFT+RIGHTARROW -- forty different columns of formulas and so you would have to enter forty different formulas all the way across.
So, what Abhijeet did -- and it's clever -- is he used the INDIRECT to go up and grab the name from F2.
So, first thing he does is check to see if the age is greater than or equal to B3; second thing, see if the age is less than or equal to the ending age in C3; and then check to see if the heading, the range name, up in F2 is not equal to blank.
Alright.
Now, the problem that we have here is I'm just going to come out to a cell that no one is pointing to.
I'm going to type the number 1 and I'll press ENTER and it's still calculating.
There. It finished. Alright. Now, how long was that?
Well, here, I actually have a macro that’ll tell us how long that was, thanks to Charles Williams. I took his code.
CONTROL+SHIFT+C, we just type the number 1 in, it takes 1.53384 seconds to calculate this spreadsheet.
Now, is that bad?
Well, yeah, it is bad especially if you're doing a lot of data entry, and Abhijeet says, hey.
I'm about to make this much more complicated by checking the city name or something like that.
I’m adding more features in so I'm going to exponentially grow from 1200 formulas to even more formulas and it's just going to get worse.
So, the problem that we have, and when I enter a number out here, none of the cells in this range are referring to this number, but because Excel doesn't know what INDIRECT it’s pointing to, it has to go through and recalculate every single formula, all 1200 INDIRECT.
All 1200 formulas that contain INDIRECT have to get recalculated which is going to take a lot of time.
So, 1.53 or something like that, 1.53 seconds was what we had before and I said to Abhijeet, I said, this is going to be a horrible set of steps.
You're not going to believe this but it's going to actually make your life easier.
I want you to come to the very first formula and we're going edit that formula and we're going to make it into text.
So I'm going to say, it's going to be =, “, and then the ^, and, at the very end, I'm going to add a ”. Alright.
So, what I've done now, in addition to adding the ^ character in, is I've created a formula that is the text of the original formula.
Alright, but now we have to do a few more things.
Anytime that there was a quotation mark in this form, then we have to replace it with “” because that's how you put a quote inside of quotes, so “”. Even if it looks silly out here, when we have this…so you need a “ there, “ there.
Alright, so, you go through it and double up all of the quotes inside.
Alright.
That's step two.
Step one, add this little bit at the beginning; I guess step two is add the quotation at the end; and then, step 3, anywhere in the middle where there's a “, put an extra “, okay?
And then, when we get to the point where they're putting &B3, right there after the [ unintelligible n sign. I’m going to close the “, put an & so that will put whatever is in B3, and then after B3 put an & and a “, and then -- the same thing out here -- after this ampersand, close the “, an & joins whatever's in C3, and then an & and a “. When we get to the INDIRECT -- now this is the fun part -- so, we actually have that , there, do a “&. We can get rid of the word INDIRECT in the (, leave E$2 there, and then another & and a “ in place of the ). Alright.
Press ENTER.
Now, this formula is a very long formula.
If we would take a look, it is replicating the original formula but it's putting in the range name right there.
Okay.
So, undo.
I'm going to take that formula now.
I’m going to copy the formula, CONTROL+C, CONTROL+SHIFT+DOWNARROW, CONTROL+SHIFT+RIGHTARROW, CONTROL+V, and copy that formula throughout, and these next two steps are incredibly important or the whole thing won't work.
We have to copy all of those formulas and PASTE as VALUES.
Alright.
So, what we've done now is we've used a formula to build the formula and the starting ^= is a common trick to make sure that these formulas are not evaluated right now, but now that we've converted the text back to values, CONTROL+H, and change your recurrence of ^= to just an = sign, REPLACE ALL, and Excel goes through, makes those replacements, and we have the calculation.
Boy, that was an insane amount of work, right, but here is the reason why we did that.
It was 1.53 seconds to calculate before.
Now, run the macro -- CONTROL+SHIFT+C -- .0032 seconds, .0032 seconds.
That is a tremendous speed improvement there.
So, it's currently calculating in two-tenths of a percent of the time that it calculated before.
Alright.
Now, I sent this back to Abhijeet and Abhijeet said, well, that's great but now you've essentially hard-coded the ages, so we're saying > or = 0, < or = to 3, and while I can live with that, that is not the best way to go at all, alright?
So, that was method number one which is all formulas and requires no VBA.
I'm going to close this workbook, FILE, CLOSE, DON’T SAVE, and reopen the workbook.
Okay, so now we're back to the version that takes 1.53 or 1.61 seconds to calculate, and I'm going to switch over to VBA -- ALT+F11 -- where I have this code that I wrote.
So, the idea is going to be I'm going to select all of the cells that contain the INDIRECT formula, we're going to loop through each cell in the selection, get the old formula, figure out where the INDIRECT starts.
So, we take the left part of the old formula to get the start of the new formula and then we go out nine characters because INDIRECT + the ( is 9 characters, and, from there, we want to figure out what's inside of the INDIRECT.
Alright, so, the goal is to start looking at characters until we encounter the ).
Now, in this case, it's just going to be really simple.
It's going to be something like C$2 or E$2 or F$2, but let's say you have something more complex inside the INDIRECT.
You would collect all of that and then pass that to the range function.
So, the range…I've stored my range.
This is going to be C$2, D$2 -- in this case, really simple -- pass that to the range and see what the current value of that is.
So, in essence, this line right here is doing the INDIRECT.
We've then stored that and a variable so that we have the original left part of the formula, the new text, and then everything after the INDIRECT ). Once we have all that, write that back to the cell formula, and then the EXIT.FOR to go back up to the next cell. It exits this for i=1.
What we're looking for the stuff inside the INDIRECT.
Alright, so, just a quick little bit of code here.
Let's switch back to Excel, ALT+F8, CHANGEINDIRECT, click RUN.
Alright.
So, now the INDIRECT is no longer there.
I've used the VBA to write the formulas, and let's run Charles William’s macro here to see how long it takes.
CONTROL+SHIFT+C. Even faster now -- .0024 seconds -- than using the formula method, and the beautiful thing is, if I would change something here, the formulas continue to update throughout.
So using VBA…well, both methods were horrendous but either method is easier than writing forty different formulas going across.
Alright, there you go.
Okay, so, twice this week I've used INDIRECT and it worked out great because we only had thirty-six cells that were using INDIRECT.
When you come up with 1200 cells using INDIRECT and each one's looking at 5000 rows, it can get really slow.
So, I want to thank Abhijeet for this great question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
