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.
0.004454, 0.00004436, and 0.0004853.

What should those values be with 4 significant figures?

 

[JoeU2004]

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 ----

 

[Rick Rothstein]

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 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.

 

[JoeU2004]

Errata...

=--(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 -----

 

[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]

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.

=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]

Yet another errata, just for posterity....

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 -----

Errata...

=--(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 -----

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 ----

 

[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 -----

 

[Ron Rosenfeld]

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

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]

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]

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]

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