[thrive_leads id=’2430′]
Welcome back for video two in this series on seven efficient ways to create accurate engineering calculations in Excel. Thanks so much for checking out the first video and for leaving your comments and feedback. In that video, I covered how to use the convert function to convert between systems of units directly in Excel. Using the convert function makes your spreadsheets less error prone and easier to understand than when conversion factors are hard coded into a formula.
I also showed you how to create named cells both individually and in a batch process as well as how to update formulas containing cell references to use assigned names. Using named cells instead of cell references makes formulas much more readable and in turn easier to verify.
I also talked to you about how Excel is really the best all around tool engineers can use for performing calculations and analyzing data and that by mastering advanced Excel techniques you’ll be able to perform your engineering work more efficiently and accurately, which will make you extremely valuable at work.
Using these same techniques and many more that I picked up over the last decade and more in engineering I’ve been able to leverage Excels capabilities to proficiently analyze hundreds of thousands of lines of data and create calculation tools in Excel that are precise and save hundreds of hours and thousands of dollars every year. I’ve even been awarded at an international technical conference for a model I built in Excel and I’ve put together this three part video series to help you work more efficiently and accurately in Excel also.
Here in video two, I’ll show you how to perform advanced look ups for engineering calculations, so let’s get into it.
The lookup table that we’re going to build will allow us to look up air properties by temperature and also do the opposite to look up temperature based on properties. Now, you may never have a need to look up temperature of air based on a known property of air, but it’s a nice little example that we’ll use in this quick demonstration here.
Now before we build the lookup table, we have to have some data that we’re going to import and for that we’re going to pull some data from the internet. I’ve already found that data. I went out to the good old engineering toolbox.com and I found a table of air properties based on temperature, so we’ve got all those properties here. Temperature is in the far left column. We’ve got specific heat ratio, specific heats, dynamic viscosity, thermal conductivity, and so on.
And so what we want to do is use Power Query to pull this data into Excel. So to do that, the first thing I need to do is copy the URL right here. I’ve included a link to this page down below this video on the page so that you can go there and get the URL yourself. So I’m just going to use control C to copy that out of here and now I can minimize the web browser. I don’t need to use the web browser anymore.
And we’re going to create a query in Excel using power query. And to do that, we’re going to go to the data tab. We’re going to select new query from other sources and then from web and once this window opens, then we can just use control V to paste that URL right into the box here and click okay.
Now Excel’s going to go out to that page and look for data sources which are going to then be displayed here on the left side. Okay, so it’s pulled that in and we can see there’s actually only one table on that page. We can click it here to preview it and here’s that table that’s been pulled in by power query and the formatting has been removed. It’s been cleansed a little bit, so we get just the data.
Now we could load this directly into our spreadsheet just the way it is, but I want to do a few edits on it just to show you how that works. So we’ll click edit. So the only thing that we’re going to need for this demonstration are the columns for specific heat and density so we can get rid of all of the other columns. Now, one way to do that is to just go ahead and click all the columns. I’m gonna, hold down the control key and get rid of everything I don’t need, so that’s everything but density and constant pressure, specific heat. Okay, got that. Selected all those columns that I don’t need. Now I can just click on remove columns.
Now power query is remembering all the steps we’ve taken on this data to clean it up and that’s really handy if you want to refresh a data source from the Internet. In this case, we’re going to pull it in one time, but if we were to refresh it from the Internet, it would be really handy. I could delete this step if I performed an operation on the table that I didn’t want to keep. You just select delete and now that restored all the columns back to the table.
To get rid of them again I can do it a little bit differently. I could select the columns I want to keep, so the temperature, the specific heat, the density, and I can click this arrow by remove columns and select remove other columns so it will then keep the columns that I’ve selected.
Okay, so now we’ve got this table the way we want it. We’ve just got to column for temperature, a column for specific heat, and a column for density. We can go ahead and load this into our worksheet clicking the Arrow right here next to close and load. I’m going to select close and load two because I want to specifically choose the location to place the table in my spreadsheet. Okay, so now this window shows up. It says load to asking me where I would like to put the data in the workbook. I’d like to put it into a table and I’d like to put it in the existing worksheet at cell B4. So we’ll click load and there’s a table loaded right into our worksheet.
So this table, if you’ll notice, has also been given the name, table zero. We can see that over here in the query window. We can also see it if we select all of the data in the table, table zero is showing up in the name box, so that’s a name that we can reference later on in our calculations when we want to do look ups with this table. We want to refer to the whole table.
You can just use the name table zero, but I’d also like to create some names for the individual columns within the table itself, just to make them a little bit easier to work with, so we’ll call them temperature. It’s called temp. The specific heat we’ll call CP. And the density. Now to select that I’m just going to click the first cell in the table, hold control shift and then down arrow to select the entire column. We’ll call that [00:06:40row] the Greek letter.
Okay. We don’t need to see this workbook query anymore, so we’ll go ahead and close that out and the first thing we’re going to do is look up density as a function of temperature. We’ll use the V lookup function, which is a function you’re probably already familiar with and we’ll use it to look up this data. And by doing so, we’ll talk about some of its limitations and why it’s not useful for advanced lookup functionality and how we can do better.
[thrive_leads id=’2430′]
So the first thing I’m going to do is enter in a temperature that we want to look up 525 Kelvin and then in the cell below that I’m going to type in the function name. If you look up, of course the first thing we need to select is the lookup value 525, and then we can type in the name of the table as the argument for the table array, that was table or score zero. Column index number is the column in the table we want pulled data from which is column three in this case for density. And then finally we’ll have to use approximate match here because we haven’t done anything to ensure that the temperature value entered in that cell above is going to match up with the temperature and the table so we’ll type true and hit enter. So V look is going out finding the greatest value that is less than the lookup value. So that’d be a temperature of 500 K, and then returning a value of 0.706 right here to this cell.
Now there are three limitations to V look up that really limits its usefulness for more advanced look ups. Number one, it can only look in the first column of the table array for the lookup value. Related to that, the column for the return value always has to be to the right of the column for the lookup value. So for instance, with V lookup, we cannot look up a value in this column and return a value from this column. And finally, the first column needs to be in ascending order. So if I would change the order of this column to descending order by sorting largest to smallest the V look up function fails.
Now we can address the shortcomings of V lookup and add even more functionality by using the index and match functions. So how do these functions work? Let’s take a look. The index function returns a value from an array or table in a given position. So in other words, to get a value back, we need to tell the array we want to look in and the position in that array. Now syntax looks like this, where a ray is the range of cells from which we want to return a value. Row numb is the row in the array from which a value is returned, call numb, which is an optional argument, is the column in the array from which a value is returned.
And the second function we’ll use match, returns the position of a value from a range of cells. So in other words, we provide the value we want to find in a column and match tells us the position where that value is found and it’s syntax looks like this where lookup value is the value to be found in the lookup array, lookup array is the group of cells to be searched, and then the match type, which is an optional argument, tells match how to deal with mismatches between the lookup value in the data. So this match type argument is kind of the trickiest part of the match function because it affects the value that is returned. If one is entered, match finds the largest value that is less than or equal to the lookup value. We’d use this if the lookup array is in ascending order.
If zero is entered, match is going to find the value that’s exactly equal to the lookup value, so we can use this argument if the data is ascending, descending, or randomly ordered. And finally, if the match type is minus one, then match is going to find the smallest value that is greater than or equal to the lookup value. And we’d use this argument if the lookup array is in descending order.
By combining index and match together we knew much more powerful look ups than with the V look up function. With V lookup we were limited to finding the lookup value in the first column of the table array, but with index and match, we have complete flexibility to look up data and return values from any two columns. They can be in any order and they don’t even need to be on the same worksheet.
So let’s try looking up a temperature given a specific heat. So I’ll enter a value for the specific heat of 1.03. And then to do this lookup, we’ll start with the index function. So we want to return a temperature. So for the array I’ll just enter the name temp to get that first column over in our table and then to find row num we’re going to use the match function. Now look up value is of course the specific heat value that was entered in the cell above. The lookup array is the column of specific heat which we’ve named CP and since we may not have an exact match and the specific heat is in ascending order, we can use the match type one. You’ll close out the parentheses and hit enter. We get 500 K so that lines up with a specific heat of 1.0295 which is the greatest value of specific heat that is less than or equal to the lookup value.
V lookup was limited to only finding values when the lookup array was in ascending order, but with index and match we don’t have that limitation. So we could look up temperature as a function of density and as you can see the density here is in descending order. So I’ll enter a density of 0.6 and then create the lookup function here and we want to return a temperature again. So temp is the array we’re going to look in and for the numb we’ll use match to look up this value in the array named row. and this time since row, for the density, is in descending order, we’ll use a match type argument of minus one. And at our closing parentheses and hit enter. Now when the match type is negative one match is going to find the smallest value that is greater than or equal to this lookup value. So that is 0.6418 which corresponds to a temperature of 550 Kelvin. And that’s what’s been returned right here.
Now say instead we want to get the largest value that is less than or equal to the lookup value. We can also modify that formula. So because the density is in descending order here, I can go into the formula and I can add one to the value return by match, so now it’s returning a value of 600 K, which is the temperature corresponding to a density of 0.5883, which is the largest density that’s less than or equal to the lookup density.
So that’s some of the nice flexibility you have by using the index and match functions to create these lookup formulas in Excel. You can do many more things than you can by just using V lookup alone.
In the next video, I’m going to share what I consider to be the ultimate efficiency and accuracy boosting technique in Excel. Creating and using user defined functions or udf’s. We can VBA or visual basic for applications to create custom functions that can be used over and over again, and I’ll show you a few expert tips to make them easier to work with as well.
That’s it for this video. Thanks again for watching. See you in the next video.
[thrive_leads id=’2430′]
