Alternatvely, following Harlan's leading, and breaking the coincidental
cohesion (shame on me!)....


Function vround(val As Double, sig As Integer) As Double
Const maxsig As Integer = 15
If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig
vround = --Format(val, "." & String(sig, "0") & "E+0")
End Function


or the Excel formula:

=--text(A1,"."&rept("0",B1)&"E+0")


Function vtrunc(val As Double, sig As Integer) As Double
Const maxsig As Integer = 15
Dim s As String
If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig
' maxsig zeros; alternatively: "."&String(maxsig,"0")&"E+0"
s = Format(Abs(val), ".000000000000000E+0")
vtrunc = Sgn(val) * (Left(s, sig + 1) & Mid(s, maxsig + 2, 5))
End Function


or the Excel formula:

=sign(A1)*(left(text(abs(A1),".000000000000000E+0" ),B1+1) &
mid(text(abs(A1),".000000000000000E+0"),17,5))


where A1 is the value and B1 is the number of significant digits.


----- original message -----

"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
Num = Format(Value, "0.##############################e+0;;0")

I'm curious: why do you have more fractional digits than can be
formatted?

For VB 6 in Excel 2003, Format does not seem to format beyond 15
significant digits. Does VB in Excel 2007 format at least 31?

Anyway, the following function handles both rounding and truncating to a
variable number of significant digits. Change maxsig to 31, if
appropriate.

Function vround(val As Double, sig As Integer, Optional trnc As Boolean =
False) As Double
Const maxsig As Integer = 15
Dim s As String, dig As Integer
If sig <= 0 Then sig = 1 Else If sig maxsig Then sig = maxsig
dig = IIf(trnc, maxsig, sig)
s = Format(Abs(val), "." & String(dig, "0") & "E+0")
vround = Sgn(val) * (Left(s, sig + 1) & Mid(s, dig + 2, 5))
End Function

Usage:

Round: =vround(A1,4)

Truncate: =vround(A1,4,1)


----- original message -----

"Rick Rothstein" wrote in message
...
Perhaps you could use this UDF (user defined function) to do that...

Function RoundSignificantFigures(Value As Variant, _
Significance As Long) As Double
Dim Num As String
Dim Parts() As String
Num = Format(Value, "0.##############################e+0;;0")
Parts = Split(CStr(Num), "E", , vbTextCompare)
If CDbl(Parts(0)) = 0 Then
RoundSignificantFigures = 0
Else
RoundSignificantFigures = CDbl(Format(Parts(0), "0" & _
Left(".", -(Significance < 0)) & _
String(Significance - 1, "0")) & _
"E" & Parts(1))
End If
End Function

Just put your calculations inside a call to this function and specify 4
for the last argument. For example, if your cell has this simple SUM
function call

=SUM(A1:A100)

then you could change it to this...

=RoundSignificantFigures(SUM(A1:A100),4)

--
Rick (MVP - Excel)


"Crystal" wrote in message
...
Here is my dilema: Have a huge spread sheet that takes hours of manual
calculations and turns it into a 5-10min process. However i am in the
process
of validating it but in my eyes its not good enough. In my spreadsheet i
need
to keep 4 significant figures in most of my showing calc's but the input
values change. So for instance, I get .004454, 0.00004436, and
0.0004853. I
need to keep 4 significant figures at all times. But i cannot use the
round
function the way that excel help has it definded. i can only get it to
keep
"X" amount of digets which is not what i want. Is there anything i can
do to
change this or is it asking to much of excel?

Thanks,
Crystal