How do I format numbers in engineering units (e.g. m, u, n)

[Guest]

How do I format my data to display in engineering units. For example if a
cell had a value of 0.000015 it is displayed as 15n and if it was 0.022 it
displays as 22m and for 34000 it displays as 34k. It must be possible to do
this but I cannot fnd it in help.

 

[Guest]

Not a format, per se, but a conversion formula........Lots of possible
variations here, but this may give you a lead..........

=IF(A1<0.0001,A1*1000000&"n",IF(A1<1,A1*1000&"m",IF(A1>1000,A1/1000&k,"")))

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks CLR. I had considered something like this but I had hoped that a
solution that operated in the same cell would be available. I'm surprised
Microsoft doesn't cater for engineers who like to express things in terms of
pF (picofarads) etc..
I'll adopt your suggestion unless anyone else has a better solution!
Thanks again.

 

[Guest]

Although I havent tried it, I'm sure that you could also set up a macro that
would go in and convert specific cells to a specific appearance......thereby
not having to use helper cells.......or there may be other ways.........as a
matter of fact I think there are several things about Excel that Microsoft
has done that they have not notified me about <G>.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Chuck, thanks again. Using macros sound like the way to go. I have little
experience of this but I'm sure anything is possible if you program it
yourself. I'll therefore explore this route. It's not essential that I solve
this problem but I'm determined not to let it beat me.
Scott

 

[Pete_UK]

One drawback with trying to do it within one cell is that the value
displayed (eg 120 pF) would actually be text, so if you needed to use
that value in some other calculation then you would have to extract it
and turn it into a number, whereas if you retain the number in the
cell with the units in an adjacent cell then you can use the number
directly.

Hope this helps.

Pete

 

[Guest]

Maybe some variation of this may be of interest.........

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'===================================================
'Converts value entered in selected cell to Engineering type display
'===================================================
If IsNumeric(Selection.Value) And Selection.Value <> "" Then
Select Case Selection.Value
Case 0 To 0.000999
Selection.Value = Selection.Value * 1000000 & "n"
Case 0.001 To 1
Selection.Value = Selection.Value * 1000 & "m"
Case Is > 1
Selection.Value = Selection.Value / 1000 & "K"
Case Else
GoTo 100
End Select
100
End If
End Sub

Vaya con Dios,
Chuck, CABGx3