format significant figs.

[ian Mangelsdorf]

Is there a way to format a number to a given number of significant figures?

Cheers

Ian

 

[AndyB]

Ian

You can format a cell to show values to a given number of significant
places. Use Format>Cells>Number tab. Just select the number of decimals you
want. This only affects what you see on the screen and not the underlying
number. To make this equal a given number of significant places you'll need
to use the ROUND function.

Andy

 

[Jan Karel Pieterse]

Hi,

I use this code to format the selected cells to show a
specified number of digits, taking into account the size
of the numbers:

Option Explicit

Sub RoundToDigits()
Dim rCell As Range
Dim dDigits As Double
Dim iRoundDigits As Integer
Dim sFormatstring As String
Dim iCount As Integer
Dim vAnswer As Variant
Dim rRangeToRound As Range
On Error Resume Next
Set rRangeToRound = Selection
If rRangeToRound Is Nothing Then Exit Sub
vAnswer = InputBox("How many digits?", "Rounding
function")
If TypeName(vAnswer) = "Boolean" Then Exit Sub
If vAnswer = "" Then Exit Sub
iRoundDigits = Application.Max(1, vAnswer)
On Error GoTo 0
For Each rCell In rRangeToRound.Cells
If IsNumeric(rCell.Value) And rCell.Value <> ""
Then
sFormatstring = "0"
If rCell.Value = 0 Then
dDigits = 3
Else
dDigits = Log(Abs(rCell.Value)) / Log(10)
dDigits = -Int(dDigits) + iRoundDigits - 1
dDigits = Application.Min(Len(Abs
(rCell.Value)), dDigits)
End If
If dDigits >= 1 Then
sFormatstring = sFormatstring & "." &
String(dDigits, "0")
ElseIf dDigits < 0 Then
sFormatstring = sFormatstring & "." &
String(iRoundDigits - 1, "0") & "E+00"
End If
rCell.NumberFormat = sFormatstring
End If
Next

End Sub



Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Harlan Grove]

Is there a way to format a number to a given number of significant figures?

Other than scientific notation?

 

[Harlan Grove]

=ROUND(A1, Sigdigits -1-INT(LOG10(ABS(A1))))
=ROUND(A1,TRUNC(-LOG(ABS(A1)))+Sigdigits-(ABS(A1)>1))

....

Not original, but I don't know whom to give credit.

=--TEXT(A1,"."&REPT("0",SigDigits)&"E+000")

 

[Jerry W. Lewis]

Not sure about the TEXT() formula, but I derived the first of the two
round formulas from first principals in 1996 and posted it to
microsoft.excel.programming in 1998

http://groups.google.com/[email protected]

It would not surprise me if someone else had independently derived it
earlier, but based on the other responses in that thread, and the fact
that John Walkenbach obtained my permission to use it in his PC World
column in 1999, I presume that earlier derivations (if they exist) are
not easily found.

Jerry

 

[Anon]

For the sake of clarification, let me just say this:
The snipping obscures the fact that I stated that both formulas I was
quoting had been posted before. I was careful not to claim any credit for
either.
This does, however, provide the opportunity to thank you for an elegant
solution!

 

[Jerry W. Lewis]

You're welcome.

FYI, I thought you were answering the OP's question, and did not think
you were taking credit for deriving the formula; sorry if you read an
unintended hostile tone.

Jerry