Another look at Formula Speed in Excel: I have to do a lookup to find both a city and a day. Rather than using a concatenated lookup, two alternative formulas that perfom faster.
Transcript of the video:
MrExcel podcast is brought to you by “EasyXL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This is a question sent in by me, I was working on a big project last week.
And I had a situation, we had a report, where you could choose various cities from the report, and then needed to show sales figures for these various points in time.
And the database back here, data, you'll see, had just thousands and thousands of rows, so for every single market there were values for numbers, you know, ranging from maybe 10 or 20, out to 70 or 80.
And we had to go find the VLOOKUP for that, and I was curious about the best way to go about this.
Now, in Excel 2007, we have the new SUMIFS command, SUMIFS.
Normally I would have done some sort of like a concatenated key back in the data, and I wanted to see if I could use SUMIFS to solve this.
So, with SUMIFS we say =SUMIFS, this is new in Excel 2007, the sum range is this value over here in column C on the data worksheet, and then we start to specify pairs of criteria.
So the first pair of criteria is: go look through column A on the data sheet, and see if it's =A1, will press F4 there.
Next criteria is: go look through the values here in column B, and see if that's equal to the particular value here in column B.
Alright, so we're doing a SUMIFS, it’s looking through just a whole bunch of data, but it does accurately get the value for Loveland, and as we change to a different city, those values update.
So, that's one way to go, but then I start to think, the problem with that, you know, talking about formula speed, is that it has to look through thousands and thousands of thousands of rows.
And I wondered if there was a faster way, although, you know, requiring a few more functions.
So the first thing I want to know is: where does the records for Loveland start?
So I use a MATCH function, so find Loveland over here in data, look through column A, and tell me where the first place that we have Loveland.
And says “That's in row 11177.” And then see how many records we have for Loveland.
Now, in a perfect world there would be the same number of records here for every city, but you know, in real life it's not the same number, and there's, you know, days missing here and there.
So, we're going to do =COUNTIF, look through those cities back there in column A, and see how many of them are equal to Loveland there, and we'll see that there's 53 records for Loveland.
Now what can I do?
Can I use that information in my VLOOKUP to make the VLOOKUP look at only those 53 records?
Alright, so here's what we do, =VLOOKUP, of course, I want to look up this value here.
Ah, but then, when we get to the table array, rather than specifying that whole big table of 10000 rows or 8000 rows or whatever it is, we use the OFFSET.
The OFFSET says “Hey, we're going to start back here on the data worksheet up in cell A1, and from that cell I want to go down a certain number of rows.
How many rows do I want to go down?” Well, that is this value up here in C1, will press F4, -1, and then over some number of columns, well from the city, we actually want to go over one column.
Specify the height, that's this value here, it's going to be different for every city.
And the width is two columns, because I have the day value, and then the actual value that I want to return, alright.
So that OFFSET function actually becomes the table array.
And then I want the second column, ,FALSE , just like normal, and we'll put that in, double-click to copy it down, OK.
And both answers work just fine, looking at the right values.
Now, let's take a look at formula speed to see which one is faster.
Alright, so we have this value, we're going to press my macro here, and it takes .354 of a second to calculate the first one.
Try the second method and press F9, look at that, so from 3/10 of a second down to .02 of a second.
7 times faster to go through the relatively painful process of setting up these two helper cells up here, out of the way.
But it makes the VLOOKUP faster, because it's only looking at, well, 67 rows in this case, instead of 10000 rows, much, much faster way to go.
So, hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This is a question sent in by me, I was working on a big project last week.
And I had a situation, we had a report, where you could choose various cities from the report, and then needed to show sales figures for these various points in time.
And the database back here, data, you'll see, had just thousands and thousands of rows, so for every single market there were values for numbers, you know, ranging from maybe 10 or 20, out to 70 or 80.
And we had to go find the VLOOKUP for that, and I was curious about the best way to go about this.
Now, in Excel 2007, we have the new SUMIFS command, SUMIFS.
Normally I would have done some sort of like a concatenated key back in the data, and I wanted to see if I could use SUMIFS to solve this.
So, with SUMIFS we say =SUMIFS, this is new in Excel 2007, the sum range is this value over here in column C on the data worksheet, and then we start to specify pairs of criteria.
So the first pair of criteria is: go look through column A on the data sheet, and see if it's =A1, will press F4 there.
Next criteria is: go look through the values here in column B, and see if that's equal to the particular value here in column B.
Alright, so we're doing a SUMIFS, it’s looking through just a whole bunch of data, but it does accurately get the value for Loveland, and as we change to a different city, those values update.
So, that's one way to go, but then I start to think, the problem with that, you know, talking about formula speed, is that it has to look through thousands and thousands of thousands of rows.
And I wondered if there was a faster way, although, you know, requiring a few more functions.
So the first thing I want to know is: where does the records for Loveland start?
So I use a MATCH function, so find Loveland over here in data, look through column A, and tell me where the first place that we have Loveland.
And says “That's in row 11177.” And then see how many records we have for Loveland.
Now, in a perfect world there would be the same number of records here for every city, but you know, in real life it's not the same number, and there's, you know, days missing here and there.
So, we're going to do =COUNTIF, look through those cities back there in column A, and see how many of them are equal to Loveland there, and we'll see that there's 53 records for Loveland.
Now what can I do?
Can I use that information in my VLOOKUP to make the VLOOKUP look at only those 53 records?
Alright, so here's what we do, =VLOOKUP, of course, I want to look up this value here.
Ah, but then, when we get to the table array, rather than specifying that whole big table of 10000 rows or 8000 rows or whatever it is, we use the OFFSET.
The OFFSET says “Hey, we're going to start back here on the data worksheet up in cell A1, and from that cell I want to go down a certain number of rows.
How many rows do I want to go down?” Well, that is this value up here in C1, will press F4, -1, and then over some number of columns, well from the city, we actually want to go over one column.
Specify the height, that's this value here, it's going to be different for every city.
And the width is two columns, because I have the day value, and then the actual value that I want to return, alright.
So that OFFSET function actually becomes the table array.
And then I want the second column, ,FALSE , just like normal, and we'll put that in, double-click to copy it down, OK.
And both answers work just fine, looking at the right values.
Now, let's take a look at formula speed to see which one is faster.
Alright, so we have this value, we're going to press my macro here, and it takes .354 of a second to calculate the first one.
Try the second method and press F9, look at that, so from 3/10 of a second down to .02 of a second.
7 times faster to go through the relatively painful process of setting up these two helper cells up here, out of the way.
But it makes the VLOOKUP faster, because it's only looking at, well, 67 rows in this case, instead of 10000 rows, much, much faster way to go.
So, hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
