how do i format a cell in Excel to 3 significant numbers

[Dave]

I need to format cells so that they only show 3 significant figures.

1.235 = 1.23
10.25 = 10.3
103.25 = 103

etc

 

[Bernard Liengme]

This will round to 3 sig figs =ROUND(A1, 3 - 1 - INT(LOG10(ABS(A1))))
BUT there is no simple way of stopping Excel showing no-sig trailing zeros
That would need a VBA subroutine

 

[Bernard Liengme]

Addendum: on the website
http://www.vertex42.com/ExcelTips/significant-figures.html

I found this User defined function
Function ROUNDSF(num As Variant, sigs As Variant) As String
Dim exponent As Integer
Dim decplace As Integer
Dim fmt_left As String
Dim fmt_right As String
Dim numround As Double
If IsNumeric(num) And IsNumeric(sigs) Then
If sigs < 1 Then
' Return the " #NUM " error
ROUNDSF = CVErr(xlErrNum)
Else
If num = 0 Then
exponent = 0
Else
'Round is needed to fix a ?truncation?
'problem when num = 10, 100, 1000, etc.
exponent = Round(Int(Log(Abs(num)) / Log(10)), 1)
End If
decplace = (sigs - (1 + exponent))
numround = WorksheetFunction.text(num, "." & _
String(sigs, "0") & "E+000")
If decplace > 0 Then
fmt_right = String(decplace, "0")
fmt_left = "0."
Else
fmt_right = ""
fmt_left = "0"
End If
ROUNDSF = WorksheetFunction.text(numround, _
fmt_left & fmt_right)
End If
Else
' Return the " #N/A " error
ROUNDSF = CVErr(xlErrNA)
End If
End Function

 

[Sloth]

I posted this same question earlier. Excel has no automatic way to format to
significant digits, except scientific notation. I've seen no formula that
will show trailing zeros (ie 10.9999 will show as 11 and not 11.0), but you
can try these formulas and format the cells to general.

=LEFT(TEXT(A1,"0.00E+00"),4)*10^RIGHT(TEXT(A1,"0.00E+00"),3)
change "0.00E+00" to represent the number of signifiacant digits.

=ROUND(A1, A2 - 1 - INT(LOG10(ABS(A1))))
A1 is the number and A2 is the number of significant digits.