#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 2nd argument in writing that formula, Excel provides an intellisense dropdown for the selection of units.

Convert1

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 2nd and 3rd 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

Advertisements

2 thoughts on “#Excel: A Model Using the CONVERT Function Containing Categories by David Hager

  1. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s