Simplify computer-aided engineering with scientific-to-engineering conversion

The simple yet useful formula in this Design Idea enables conversion from scientific format (for example,
2.2^–9), which is typical for CAE (computer-aided-engineering), double-precision output values, into "human-friendly" engineering format (for example, 2.2 nF). The engineering format is more suitable for bills of material and other electrical and electronic-engineering documents and specifications.

The formula is rather straightforward. It takes two parameters. The first is the numerical value, and the second one specifies the unit of measurement—ohms, farads, or henries, for example. Alternatively, it could be of any random text, including an empty string, "." The formula calculates the mantissa/order of magnitude and returns the text string, formatted in compliance with commonly accepted electrical-engineering practice. Listing 1 shows details of the formula. The tricky part of the formula is the conversion to a decimal type after the formula calculates the ratio of two log values (Reference 1). This step ensures the correct order-of-magnitude calculations in cases in which the mantissa of the input value is close or equal to one.

The formula is in VB; you could use it in any VB/VBA-backed software applications. In this example, the function is in a code module of an MS Excel file (Figure 1). You could also use it as an Excel Add-In (.xla) or a "pure-VB," compiled-DLL component. Click here to download a zip file containing both Listing 1 and the Excel file. The input numerical value in this formula has double-precision accuracy, and its range spans from approximately –1.79³⁰⁸ to +1.79³⁰⁸, which is sufficient for any practical engineering calculations. Note that the maximum value is even bigger than the famous "googol," which is represented by 100 digits.