The last few posts have been focused on calculation with a specific data set that was imported from the internet. I’ve demonstrated how to import data from the web, create a VLOOKUP function to use that data table in a calculation, and how to use data validation to prevent spreadsheet errors. Today, in the final installment of the series, we will work the last few kinks out of the data by using Excel logical functions in order to make a bulletproof spreadsheet.
The Problem
In the data that was imported from the web, there are a handful of rows that do not contain numerical values for the thermal expansion coefficient. Instead, they contain ranges (e.g. “45-65”). If one of these materials is selected, Excel cannot perform the calculation and returns a #VALUE! error.
We have to convert those ranges to a number in order for the spreadsheet to return a value when that material is selected.
The easiest short term solution is to just replace the ranges with a value. However, this is a poor long term solution because these changes would be lost if the data table was ever refreshed.
The best solution is to append a column onto the table and create a formula to return a single value that can replace the range. In this case, I will just calculate the average an average value from the high and low values of the range.
Let’s take a look at how to do that.
The Solution – Excel Logical Functions
First, I create three additional columns in the table by typing a title for the columns in cells C1, D1 and E1.
Next, I create formulas to extract the first and last values from the ranges into columns C and D, respectively.
I use a combination of the RIGHT, LEFT, LEN, and FIND functions to extract these first and last values.
Next, I create a conditional statement that can determine when column B does not contain a number using the “IF” and “ISNUMBER” functions.
Finally, I update the VLOOKUP function on the main calculation worksheet to reflect the changes to the data table. The table_array and col_index_num variables are updated because the table size has changed and the column containing the output values is now column 5.
The Result
Now the spreadsheet returns a value as expected, no matter the material selected.
