Rounding Formula

[mccrimmon]

Hi,

I am wondering if anyone can help me with the following statement.

I have a number (example 1.068501) which i need to round up to 3
decimal places or 4 significant figures if the number is greater than 1
so the result would be 1.069.

If the number was (example 0.926145) then we need to round up to 4
decimal places or 4 significant figures so the result would be in this
example would be 0.9262.

Can someone please provide me with a formula for this sort of
calculation as I have a spreadsheet of over 500 numbers!

Cheers in advance

 

[Guest]

=round(a1,3)

hth
regards from Brazil
Marcelo

"mccrimmon" escreveu:

 

[MartinW]

Hi mccrimmon,

Try =IF(A1>=1,ROUND(A1,3),ROUND(A1,4))

HTH
Martin

 

[bplumhoff]

Hello,

We discussed this in the German newsgroup recently.

Easiest presentation (worksheet function) seems to be:
=TEXT(A1,"0.000E+000")

Or you might want to use my UDF:

Function dbl2nsig(d As Double, _
Optional n As Long = 4) _
As String
'Returns string presentation of d with
'n significant digits. PB V0.01
Dim i As Long, j As Long
Dim s As String, sr As String
s = Format(d, "0." & String(n - 1, "0") _
& "E+000")
i = Right(s, 4)
Select Case i
Case Is > n - 2
sr = Left(s, 1)
If n > 1 Then sr = sr & Mid(s, 3, n - 1)
sr = sr & String(i - n + 1, "0")
Case 0
sr = Left(s, n + 1)
Case Is < 0
sr = "0." & String(-1 - i, "0") & Left(s, 1) _
& Mid(s, 3, n - 1)
Case Else
s = Left(s, 1) & Mid(s, 3, n - 1)
sr = Left(s, i + 1) & "." & _
Right(s, n - i - 1)
End Select
dbl2nsig = sr
End Function

HTH,
Bernd