#Excel: A Model Using the CONVERT Function Containing Categories by David Hager

The CONVERT function in Excel allows the user to calculate from one type of unit to another. When you get to the 2^nd argument in writing that formula, Excel provides an intellisense dropdown for the selection of units.

Unfortunately, although the units are in category order, there is no way to see a category description.

The tables containing the units used by the CONVERT function can be found at the following link.

https://support.office.com/en-us/article/CONVERT-function-D785BEF1-808E-4AAC-BDCD-666C810F9AF2

That data was imported, messaged and cleansed to make the list found in the Info worksheet, adding a category for each record in the list. I then used the Advanced Filter to make a list of unique items from column A. That is the primary list used in the calculation model (on the Convert worksheet).

Type=Convert!$C$2

And is used as a data validation dropdown in

Category=Convert!$C$2

The workbook also provides a good example of creating dependent dropdowns from a list. A great formula used to do this is shown below:

CategoryDep=INDIRECT(“Info!C”&MATCH(Category,Info!$A$1:$A$93,0)&”:C”&MAX(IF(Category=Info!$A$2:$A$93,ROW(Info!$A$2:$A$93))))

This data validation list formula is used in the dropdowns in E2 and F2.

From =Convert!$E$2

To =Convert!$F$2

In the example workbook, the cells that are formatted as light blue have Data Validation dropdowns that allow categories to be used with the unit selections for the CONVERT function. So, the dropdown in C2 is used to select the category, and the dropdowns in E2 and F2 are used as the 2^nd and 3^rd arguments in the CONVERT function in cell A2.

Note that this technique does not include the Prefix and Binary Prefix categories.

It is also important to note that since the unit information used here is in a worksheet list, we are not bound by the examples provided by Microsoft. However, a different calculation mode would have to be used, since the custom unit symbols would not be recognized by the CONVERT function. I plan to look at using a formula like =PRODUCT(number,multiplier) for the custom categories. Feel free to use this idea to build your own custom examples. I will be working on it, too 😊.

You can download the file here.

convert2