As all electrical engineers, I work with a large range of numbers for various components – from 22 pF capacitors (22* 1e-12) to 2 MΩ resistors (2* 1e+6). To make the work a little easier, we tend to use Engineering Notation. This ensures the SI prefixes are multiples of three. Just try quickly adding 4 deciFarad to 7 centiFarads and the problem shows itself (answer is 400 mF plus 70 mF = 470 mF).
As handy as MS Excel can be, it does not have a native way to display results in engineering notation. So I had to write a VBA function.
Inside any cell, just pass the value to this function along with the number of significant digits to display and any unit (such as W, L, A, etc.) and it will be appended to the displayed value. If you don’t want a unit, then pass an empty string (“”).
Example raw input and displayed values.
| Cell contents | Displayed Value |
|---|---|
| =EngSI(0.1234, 2, “V”) | 120 mV |
| =EngSI(12345, 3, “W”) | 12.4 kW |
| =EngSI(299792458, 3, “m/s”) | 300 Mm/s |
Below is the source code for the Excel VBA function.
' Function to convert value to Engineering notation.
' Handles positive and negative numbers
' from 10e-24 to 10e+24.
'
' value: input value
' sigDigits: number of significant digits.
' (i.e. 123 with 2 digits => 120)
' unit: SI units (i.e. W, m, L)
'
' Returns: String (i.e. "33.3 W", "124 kW", "-6 uW")
Function EngSI(value, sigDigits, unit)
Dim i As Integer
Dim s As String
' Add multiplier to add reducing roundoff underflow.
' If increase number of significant digits beyond 3,
' then will need to reduce this value.
value = value * 1.0005
' Get number of decimals to move decimal point to normalize numbers.
' We must use absolute value since we use log later.
' Sign is retained and will fix itself later.
Dim logVal As Double
logVal = Log(Abs(value)) / Log(10#)
' Int function will truncate value but will change negative numbers
' to next lower int.
' Int(99.2) = 99, Int(-99.2) = -100
Dim logInt As Double
logInt = Int(logVal)
' Create normalized value (+/- 1.xxx) and round to number of
' significant digits.
Dim normValue As Double
normValue = Round(value / (10 ^ logInt), sigDigits - 1)
' Create SI scale:
' [..., -2:micro, -1:milli, 0:no prefix, 1:kilo, 2:mega, ...]
' Create EngSI - value is 1.00 to 999.0 followed by SI scale prefix.
Dim siScale As Double
If value >= 1 Then
' Determine SI units for numbers greater than or equal zero.
If (logInt Mod 3) > 0 Then
siScale = (logInt - 1) \ 3
EngSI = normValue * 10 ^ (logInt - siScale * 3)
Else
siScale = logInt \ 3
EngSI = normValue * 10 ^ (logInt - siScale * 3)
End If
Else
' Determine SI units for numbers less than zero.
If Abs(logInt Mod 3) > 0 Then
logInt = logInt - 3
siScale = logInt \ 3
EngSI = normValue * 10 ^ (logInt - siScale * 3 + 3)
Else
siScale = logInt \ 3
EngSI = normValue * 10 ^ (logInt - siScale * 3)
End If
End If
' SI Prefixes from 10^-24 to 10^24
Dim prefixes As String
prefixes = "yzafpnum kMGTPEZY"
s = ""
If siScale <> 0 Then
s = Mid(prefixes, siScale + 9, 1)
End If
EngSI = CStr(EngSI) + " " + s + unit
End Function