significant figures
Crystal
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
T. Valko
>need to keep 4 significant figures at all times.
What should those values be with 4 significant figures?
--
Biff
Microsoft Excel MVP
"Crystal" <Cry...@discussions.microsoft.com> wrote in message
news:5FD85AB9-A63E-49B3...@microsoft.com...
JoeU2004
"Crystal" <Cry...@discussions.microsoft.com> wrote:
> 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.
If the original is in B1, try:
=--(LEFT(TEXT(B1,"0.000E+0"),5) & MID(TEXT(B1,"0.000E+0"),6,5))
Note: This rounds the 5th significant digit. Is that okay? If not, then
try:
=--(LEFT(TEXT(B1,"0.0000E+0"),5) & MID(TEXT(B1,"0.0000E+0"),7,5))
----- original message ----
"Crystal" <Cry...@discussions.microsoft.com> wrote in message
news:5FD85AB9-A63E-49B3...@microsoft.com...
Rick Rothstein
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" <Cry...@discussions.microsoft.com> wrote in message
news:5FD85AB9-A63E-49B3...@microsoft.com...
Rick Rothstein
In case you are new to UDF's, they are installed as follows... click Alt+F11
to go into the VB editor, click Insert/Module from its menu bar and then
copy/paste my code into the code window that opened up. That's it... you can
now use RoundSignificantFigures just like a built-in worksheet function.
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:uQQkpR4$JHA....@TK2MSFTNGP05.phx.gbl...
JoeU2004
Errata...
I wrote:
> =--(LEFT(TEXT(B1,"0.000E+0"),5) & MID(TEXT(B1,"0.000E+0"),6,5))
> [....] =--(LEFT(TEXT(B1,"0.0000E+0"),5) & MID(TEXT(B1,"0.0000E+0"),7,5))
Those formulas do not handle negative numbers correctly
Instead, try the following (rounding the 5th significant digit):
=--(LEFT(TEXT(B1,"+0.000E+0;-0.000E+0"),6) &
MID(TEXT(B1,"+0.000E+0;-0.000E+0"),7,5))
Or the following (truncating after the 4th significant digit):
=--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) &
MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5))
----- original message -----
"JoeU2004" <joeu...@hotmail.com> wrote in message
news:epeVNz3$JHA....@TK2MSFTNGP04.phx.gbl...
Harlan Grove
...
>Instead, try the following (rounding the 5th significant digit):
>
>=--(LEFT(TEXT(B1,"+0.000E+0;-0.000E+0"),6) &
> MID(TEXT(B1,"+0.000E+0;-0.000E+0"),7,5))
Why not the far simpler
=--TEXT(B1,".0000E+000")
? Or
=ROUND(B1,INT(4-LOG10(ABS(B1))))
>Or the following (truncating after the 4th significant digit):
>
>=--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) &
> MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5))
=TRUNC(B1,INT(4-LOG10(ABS(B1))))
JoeU2004
"Harlan Grove" <hrl...@gmail.com> wrote:
> "JoeU2004" <joeu2...@hotmail.com> wrote...
> > Instead, try the following (rounding the 5th significant digit):
> > =--(LEFT(TEXT(B1,"+0.000E+0;-0.000E+0"),6) &
> > MID(TEXT(B1,"+0.000E+0;-0.000E+0"),7,5))
>
> Why not the far simpler
> =--TEXT(B1,".0000E+000")
Yes, m-u-c-h better for the rounding case. Deja vu! :)
I think "E+0" would suffice. And FYI, I write 0.000E+0 out of habit. I
don't think it makes a significant difference -- no pun intended. ;)
> > Or the following (truncating after the 4th significant digit):
> > =--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) &
> > MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5))
>
> =TRUNC(B1,INT(4-LOG10(ABS(B1))))
Well, perhaps:
=IF(B1=0,0,TRUNC(B1,INT(4-LOG10(ABS(B1)))))
I'm just a tad squemish about using the LOG function; I worry about
numerical corner cases in the binary world. But I'm probably wrong. I
agree: mathematically, it should work fine, especially since the largest
decimal exponent is relatively small (+/-308).
PS: My truncation formulation is incorrect. Try 1.23999999999999. If
there is any problem with the TRUNC expression, I would go with:
=SIGN(B1)*(LEFT(TEXT(ABS(B1),"0.00000000000000E+0"),5) &
MID(TEXT(ABS(B1),"0.00000000000000E+0"),17,5))
JoeU2004
I wrote:
> Or the following (truncating after the 4th significant digit):
> =--(LEFT(TEXT(B1,"+0.0000E+0;-0.0000E+0"),6) &
> MID(TEXT(B1,"+0.0000E+0;-0.0000E+0"),8,5))
That is wrong; try 1.23999999999999. At this point, I would opt for the
briefer form:
=SIGN(B1)*(LEFT(TEXT(ABS(B1),"0.00000000000000E+0"),5) &
MID(TEXT(ABS(B1),"0.00000000000000E+0"),17,5))
(But see Harlan's better formulas.)
----- original message -----
"JoeU2004" <joeu...@hotmail.com> wrote in message
news:OrvRIy4$JHA....@TK2MSFTNGP03.phx.gbl...
JoeU2004
> 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" <rick.new...@NO.SPAMverizon.net> wrote in message
news:uQQkpR4$JHA....@TK2MSFTNGP05.phx.gbl...
Ron Rosenfeld
wrote:
One possible issue with some of the other solutions offered is that trailing
zero's are dropped.
So, for example, 1.2 is expressed as 1.2 and not as 1.200.
One possible solution, which may or may not be acceptable on your worksheet,
would be to use scientific notation -- just custom format your numbers as
0.000E+00
You would then see:
0.004544 --> 4.544E-03
0.00004436 --> 4.436E-05
0.0004853 --> 4.853E-04
1.2 --> 1.200E+00
--ron
JoeU2004
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" <joeu...@hotmail.com> wrote in message
news:OvcL6P6$JHA....@TK2MSFTNGP04.phx.gbl...
JoeU2004
"Ron Rosenfeld" <ronros...@nospam.org> wrote:
> One possible issue with some of the other solutions
> offered is that trailing zero's are dropped.
Excellent point! We need clarification from Crystal on the requirements.
I interpreted "keep 4 significant figures" to mean change the value. You
are interpreting it to mean simply change the display. To that end, Crystal
could want the Number format with a variable number of significant digits.
And Crystal might want both: change the value and the display.
The latter (variable Number format) is doable, probably more easily in a
UDF, but perhaps feasible in an Excel formula. But I would like to see
Crystal's clarification before going off on further tangents.
----- original message -----
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:mpu855hmr7vg2r7is...@4ax.com...
Rick Rothstein
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.
Ron Rosenfeld
On Wed, 8 Jul 2009 10:02:13 -0700, "JoeU2004" <joeu...@hotmail.com> wrote:
>Excellent point! We need clarification from Crystal on the requirements.
>
>I interpreted "keep 4 significant figures" to mean change the value. You
>are interpreting it to mean simply change the display. To that end, Crystal
>could want the Number format with a variable number of significant digits.
>And Crystal might want both: change the value and the display.
>
>The latter (variable Number format) is doable, probably more easily in a
>UDF, but perhaps feasible in an Excel formula. But I would like to see
>Crystal's clarification before going off on further tangents.
Hopefully, she will post back shortly.
--ron