This file is offered for free in our downloads page. For more projects, visit our store. Keywords: conversion, form, matrix, array, validation, conditional, names, table
Purpose
My purpose here is to showcase elegant design with some advanced features, while exposing a rather esoteric function in all its glorious detail.
Any business that transacts materials in different units must have a robust conversion solution for accurate billing and internal controls. When volume data are informally analyzed in Excel, such as forecasting and inventory planning, it is common practice to hard-code multipliers. For example, to convert KG to LB, “=A3*2.2” will be close enough for most purposes. The advantage of using Excel’s built-in converter is that it is transparent, and avoids fat-finger errors and inconsistent rounding.
The CONVERT function includes 103 units across 13 properties, with 20 decimal and 8 binary prefixes. Casual users might not know it exists, or might get frustrated trying to use it when they encounter conditions like these:
The first unit pop-up list is ten pages long, sorted but not differentiated by property, with no type-ahead navigation.
There’s no indication of which units accept prefixes (only 35 do), prefixes are not listed (must be manually added) and are case-sensitive.
Technical users in academia, research, engineering, etc. depend on accurate conversion and can make better use of CONVERT when they understand how it works, and how to express large-magnitude conversions with proper use of prefixes.
In data management (bits and bytes), binary prefixes are preferred for accuracy, while users are mostly familiar with decimal prefixes. For perspective, 1 Tbyte (terabyte) = 10^12 bytes, whereas 1 Tibyte (tebibyte) = 2^40 = 1.0995 x 10^12 bytes. CONVERT handles both decimal and binary prefixes for bits and bytes, so the formula author must use them with intention.
For all these reasons, CONVERT is an opportunity to elucidate and showcase while presenting an engaging user interface without the use of VBA code. Features include:
Reference Tables to store and access data
Array Formulas to generate filtered pick-lists and a dynamic conversion matrix
Data Validation to present the pick-lists and user info, and to prevent errors
Name Manager to name input cells, and name intermediate and condition formulas
Conditional Formatting to maintain an elegant view and avoid confusion
Reference Data Sheet
While CONVERT does the actual calculation, it depends on the user’s diligent study to understand which units are compatible, and which are prefixable. To expose the details for study, a dedicated sheet is used to store and review them. In addition, the sheet provides named references for formulas and picklists. The sheet is integral to the user interface, but can be hidden if not frequently needed for manual reference.
Units
Figure 1: Units Table, showing “Prefixable” formula
The Unit Table’s 103 rows are a copy of the list of units from the CONVERT help page. This includes a long-text unit name, the short unit name used by CONVERT, and the property of relevance. To these columns I added an arbitrary but somewhat logical grouping of properties, and a calculated column to determine prefixability.
If a prefix is used with a unit that does not accept prefixes, the result is N/A. In the Prefixable column above, this fact is used within IFNA to return 2 if binary-prefixable, 10 if decimal-prefixable, and 0 otherwise. A single prefix, “ki” for binary and “k” for decimal, is used as the test.
Prefixes
Figure 2: Prefix Table
The Prefix Table is also copied from the web, with long (phonetic) and short (functional) text, and value. Here I combined decimal and binary prefixes, differentiating them by adding columns for Base and Power. Then I added a column to concatenate helpful labels for the drop-down list.
For the binary prefixes, note the reference to the decimal “approximate equivalents”, and the increasing difference in value, from 2% for “kibi” to 20% for “yobi”. Each binary prefix takes the first two letters of the decimal prefix, and adds “bi”. Of course since a bit is not divisible, there are no negative-power equivalents, and since the increment is 2^10, there is no “hecto” or “dekao” counterpart. Even if binary prefixes are seldom or never used, they are included here as a point of transparency.
As an alert to the user to pay attention to Prefix case, in addition to the info popup on the header, I highlighted prefixes that Conditional Formatting considers to be duplicates.
Formats Table
The conversion output will be displayed as text, and will therefore need to be formatted within the TEXT formula. The appropriate format depends on the magnitude of the result, and whether it is integer or has decimal places. To determine integer or not, I started with MOD(result,1), and soon learned that it raises an undocumented error above 10^12, so I used result-INT(result) instead. For magnitude I use LOG(result) and MATCH it against a list of powers of ten to find the corresponding format string. Of course the integer format is irrelevant for negative logs.
Figure 3: Format Table assigning format string to order of magnitude
This segment from the output formula formats the value using the relevant column.
TEXT(ValueOutput,INDEX(IF(ValueOutput-INT(ValueOutput),Formats[Decimal], Formats[Integer]),IFNA(MATCH(LOG(ValueOutput),Formats[LOG]),1)))
Form Picklists
The lists that supply the in-cell drop-downs are dynamic arrays. They are all single-column and are nested together, with a header row for transparency and for consistent look with the tables. As arrays, the formulas reside only in the first cell below the header. Each array spills down and is named using # notation. For example, the name “GroupList” refers to =$U$3# which is defined as =UNIQUE(Units[Group]). Each “-List” name then supplies a Data Validation picklist in the user form.
Figure 4: Form lists as array formulas, here with Distance selected in the user form
While GroupList is constant, the other four arrays change depending on user selections.
Table 1: Form Picklists
Note the “>=” in the Prefix lists, because binary-prefixable units are also decimal-prefixable.
Name Manager is used to assign names to source data and input cells, as good standard practice to enhance formula transparency. Formulas are also used in Name Manager to identify background conditions to show or hide fields, flag errors and highlight data.
User Interface
The Converter sheet is the “retail shelf” where the user interacts with the tool, and so receives the highest level of attention to technical integrity, dynamic response and aesthetic appearance. This is where designers, including myself, often turn to VBA to control the user environment. This tool is near my VBA borderline, which is probably more distant than most. I like to challenge what can be done with the simplest tools available.
First, the data are intended to stay within one screenful to avoid scrolling as much as possible. The sheet is gray-filled to look less like a spreadsheet and more like an app. Input and output cells will have the fill conditionally removed or changed for intuitive emphasis. The sheet is compact but not busy. The top five rows comprise inputs and outputs, below which is a dynamic matrix.
The first set of selections are Group and Property. When both are blank, the rest of the sheet is blank, with a message to prompt selection. When Group only is selected, the Matrix is displayed for all Properties in the Group, the Property list is filtered for the Group, and the Calculator fields remain hidden. When Property is selected, the Matrix displays the single Property, and the calculator fields are revealed. If Group is then changed causing an inconsistency, it is font-hidden and ignored.
The Calculator
The Calculator region has two columns labeled “FROM” and “TO”, and three rows for “Value”, “Unit” and “Prefix”. The resulting six cells are each merged across two sheet columns, to allow wider width while maintaining consistent column widths in the matrix below. The “FROM Value” cell is numeric input, default 1, and the “TO Value” cell is the calculated output, shaded consistent with the matrix output cells. The other four input cells are fed and enforced by Data Validation picklists, as described in the previous section.
The two Unit fields are filtered for the selected Property. This is similar to how CONVERT filters the “to unit” list to those compatible with the “from unit”, but here it applies to both units, making “from unit” selection easier.
Now here comes the risk of VBA-less design: Data Validation will not allow selection of a Unit not associated with the selected Property, but if a different Property is later selected, an inconsistency will occur. VBA would be able to clear out the units when the Property is changed. To handle this without clearing the field requires a work-around, as described next.
The work-around idea is to identify the Unit inconsistency, then use it to format the cell to inform the user. This incurs no more user effort than automated deletion, since a new Unit must be selected in either case. Error identification is accomplished by Name Manager formulas that compare the Unit’s Property to the selected Property:
UnitFromErr =IFNA(INDEX(Units[Property], MATCH(UnitFromSelect,Units[UnitText],0))<>PropertySelect,"")
The Conditional Format rule takes the error condition and changes the cell’s number format to ;;;"select a valid unit", in italic red font. Thus, if the Unit is filled and incompatible with the Property, the user is given direct feedback that it needs to be changed. See example case 7 below.
The two Prefix fields are optional, and dependent on the respective Unit. They therefore require similar error handling, in case the Unit is changed to one which is not prefixable. This response is more forgiving than Unit errors, since the invalid Prefix can simply be hidden and ignored. The interactive stages and responses are summarized below.
Table 2: User-dynamic response
As you look down each column, note how the display responds to the selections. Prefix is only open if its Unit is prefixable. If the Unit is changed to non-prefixable, the Prefix simply disappears and is removed from the conversion. If the Property is changed, Units are flagged invalid and Prefixes are hidden. The intent and effort is to present only what’s relevant to the user at all times.
Case 1: nothing selected
Case 2: Group selected
Case 3: Property selected
Case 4: Units selected, FROM Unit is prefixable
Case 5: Prefix selected
Case 6: Units changed, invalid FROM Prefix is ignored
Case 7: Property changed, Unit selections display error message
The most complex formula is the output sentence. It concatenates the inputs and outputs with both long and short units and prefixes, formats the output value, has two-level error protection, and hides when not needed.
=IFERROR(IF(LEN(PropertySelect)=0,"",IF(OR(UnitFromErr,UnitToErr),"invalid selections",CONCAT("RESULT: ",IF(ValueInput,ValueInput,1)," ",IF(PrefixFromErr,"",PrefixFromSelect)," ",UnitFromSelect," (",UnitFrom,") = ",TEXT(ValueOutput,INDEX(IF(ValueOutput-INT(ValueOutput), Formats[Decimal],Formats[Integer]),IFNA(MATCH(LOG(ValueOutput),Formats[LOG]),1)))," ",IF(PrefixToErr,"",PrefixToSelect)," ",UnitToSelect," (",UnitTo,")"))),"")
Figure 5: Example output sentence
The Matrix
Figure 6: The Matrix for the Time Property
Matrix headers for Property and Description are one array formula in cell A9:
=IFERROR(FILTER(Units[[Property]:[UnitText]],IF(LEN(PropertySelect), Units[Property]=PropertySelect, Units[Group]=GroupSelect)),"")
The “From ↓” unit headers are defined in cell C9:
=IFERROR(FILTER(Units[Unit],IF(LEN(PropertySelect), Units[Property]=PropertySelect,Units[Group]=GroupSelect)),"")
This “From unit” header column must be its own one-column array in order to be transposable to the “To unit” header row, defined in cell D8:
=TRANSPOSE(C9#)
These dynamic headers define a square matrix filled with one simple formula using named arguments:
In Name Manager, with active cell D9, MatrixFrom = $C9, MatrixTo = D$8
All Matrix interior cells =IFNA(CONVERT(1,MatrixFrom,MatrixTo),"")
CONVERT does not accept array arguments, so this formula must be copied to every possible matrix cell. To ensure I covered the largest matrix extent, I selected the Group with the most Units in it, and extended the formula to the end of the headers. This turned out to be a 63x63 matrix at maximum. With irrelevant data hidden, the visible size is dynamic to user selections. Below shows how the matrix body is formatted.
Figure 7: Matrix body formatting
Conclusion
This project has showcased a number of Excel features that support and enhance elegant design and has exposed CONVERT function nuances in detail. For more examples, visit our store.
Comments