See inline comments...
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?
The function I posted was a modified version of an old function I had
written for the compiled
VB world. That function originally returned a
Variant (I changed the return type to Double because the OP wanted the
function for the spreadsheet, which also necessitated the CDbl check for 0).
The reason I returned a variant is because the Format function is not
limited to 14 significant digits as you have said... it can round values
(passed in as String values) up to 29 digits without a decimal point and 28
digits with a decimal point (it appears to be using a Decimal sub-type of a
Variant for its first argument). You can see this with this example (run it
in the Immediate window)...
? Format("123.456789098765432101234567898765432", "0." & String(25,"#"))
This will return an answer of 123.4567890987654321012345679. Of course, to
hold the accuracy, this value must be preserved as a String (like when you
would assign it to a TextBox or concatenate it with other text). While I am
having trouble relocating my original function, here is the function I
posted modified to handle more than 15 significant digits.
Function RoundSignificantFigures(Value As Variant, _
Significance As Long) As Variant
Dim Num As String
Dim Parts() As String
Num = Format(Value, "0.##############################e+0;;0")
Parts = Split(CStr(Num), "E", , vbTextCompare)
If Parts(0) = 0 And UBound(Parts) = 0 Then
Else
RoundSignificantFigures = Format(Parts(0), "0" & _
Left(".", -(Significance < 0)) & _
String(Significance - 1, "#")) & _
"E" & Format(Parts(1), "#00")
End If
End Function
You can use this inside of VBA code as necessary or on a worksheet if the
cell providing the first argument is formatted as Text...
Function RoundSignificantFigures(Value As Variant, _
Significance As Long) As Variant
Dim Num As String
Dim Parts() As String
Num = Format(Value, "0.##############################e+0;;0")
Parts = Split(CStr(Num), "E", , vbTextCompare)
If Parts(0) = 0 And UBound(Parts) = 0 Then
Else
RoundSignificantFigures = Format(Parts(0), "0" & _
Left(".", -(Significance < 0)) & _
String(Significance - 1, "#")) & _
"E" & Format(Parts(1), "#00")
End If
End Function
Anyway, the following function handles both rounding and truncating to a
variable number of significant digits. Change maxsig to 31, if
appropriate.
I just wanted to mentiont than the function I posted works fine even with
the extra # signs.
--
Rick (MVP - Excel)
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