significant figures problem

[BAlGaInTl]

Hello all...

I'm using a formula that I found elsewhere on the board to rount a number based on significant figures. This is a sample of what I'm using.

ROUND(G32,B11-1-INT(LOG10(ABS(G32))))

Where G32 is the number you want to round, and B11 is the number of sig. figs. desired.

Here is the problem that I have found. It seems that Excel insists on dropping the last number if it is a zero. So, in effect, what you get is not correct if the last significant figure is a zero.

Example. Lets say that I want 2 significant figures.

0.00123 will correctly display as 0.0012

However,

0.00101 will display as 0.001, which is only 1 significant figure, and not 2.

This only happens when the last number is a zero.

Help?

 

[goblin]

What exactly are you going to use this for? Is there anything that stops you from just putting the variance of the number in the adjacent cell?

 

[BAlGaInTl]

Ok...

Almost there

I started with this code

Function SF(Val As Variant, Fig As Variant) As Variant
    Dim x As Variant
    Dim k As Variant
    Dim i As Boolean
    Dim j As Boolean
    x = Fig - 1 - Int(WorksheetFunction.Log10(Abs(Val)))
    i = Mid(Val, InStr(1, Val, ".") + x, 1) = "0"
    j = Int(Mid(Val, WorksheetFunction.Min(Len(Val), InStr(1, Val, ".") + x + 1), 1)) < 5
    k = Len(Val) - WorksheetFunction.Find(".", Val)
    If k < x Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), k + (x - k)) & "0"
    ElseIf i And j Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), x + 1)
    Else
        SF = WorksheetFunction.Round(Val, x)
    End If
End Function

As you can see, I added another variant so that I could check to see if there are enough decimal places to begin with....

That way... If I want 0.12 to 3 sig figs, I would want 0.120 reported.

With that in mind, I added another statement to the If statement, and then appended an & "0" to the end of it to place an extra zero.

However, it is possible that you could need more...

ie. 0.12 to 4 sig figs would be 0.1200

Is there a way that I can make that IF statement add (x - k) zeros to the end of the number?

It is almost there...

 

[Aladin Akyurek]

Ok...

Almost there

I started with this code

Function SF(Val As Variant, Fig As Variant) As Variant
    Dim x As Variant
    Dim k As Variant
    Dim i As Boolean
    Dim j As Boolean
    x = Fig - 1 - Int(WorksheetFunction.Log10(Abs(Val)))
    i = Mid(Val, InStr(1, Val, ".") + x, 1) = "0"
    j = Int(Mid(Val, WorksheetFunction.Min(Len(Val), InStr(1, Val, ".") + x + 1), 1))< 5
    k = Len(Val) - WorksheetFunction.Find(".", Val)
    If k< x Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), k + (x - k)) & "0"
    ElseIf i And j Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), x + 1)
    Else
        SF = WorksheetFunction.Round(Val, x)
    End If
End Function

As you can see, I added another variant so that I could check to see if there are enough decimal places to begin with....

That way... If I want 0.12 to 3 sig figs, I would want 0.120 reported.

With that in mind, I added another statement to the If statement, and then appended an & "0" to the end of it to place an extra zero.

However, it is possible that you could need more...

ie. 0.12 to 4 sig figs would be 0.1200

Is there a way that I can make that IF statement add (x - k) zeros to the end of the number?

It is almost there...

Which is (most) correct?

sdible.xls
	A	B	C	D
1		SigFigs	Formula	SF
2	0.00123	2	0.0012	0.0012
3	0.00101	2	0.0010	0.0010
4	1.23457E-05	2	0.000012	0.000012
5	12.345	2	12.34	12
6	0.1234	2	0.12	0.12
7	0.00123	2	0.0012	0.0012
8	0.12	3	0.120	0.120
9	0.00126	2	0.0013	0.0013
10	0.12	4	0.1200	0.120
11
Sheet1

The formula in C2 is...

=INT(A2)&IF(B2,"."&SUBSTITUTE(SUBSTITUTE(MOD(A2,1),"0.","")&IF(LEN(SUBSTITUTE(MOD(A2,1),"0.",""))< B2,REPT(0,B2-LEN(SUBSTITUTE(MOD(A2,1),"0.",""))),""),--SUBSTITUTE(MOD(A2,1),"0.",""),IF(LEN(SUBSTITUTE(MOD(A2,1),"0.","")) > 2,LEFT(ROUND(--SUBSTITUTE(MOD(A2,1),"0.","")/10,0)*10,B2),LEFT(--SUBSTITUTE(MOD(A2,1),"0.",""),B2))),"")

 

[tusharm]

The code below has been tested for +/- 0.0226, 10.13, 110, 0.12, 0.18, 1e+6, 1e-8, each number calculated to significant digits (0,1,2,3,4,5)

Option Explicit
    Function Log10(ByVal x As Double)
        Log10 = Log(x) / Log(10#)
        End Function
Function NbrToSigDigits(ByVal x As Double, ByVal nbrDigits As Long)
    Dim DigsInNbr As Long, numericRslt As Double, Rslt As String
    With Application.WorksheetFunction
    numericRslt = .Round(x, nbrDigits - Int(Log10(Abs(x))) - 1)
    Rslt = CStr(numericRslt)
    DigsInNbr = Len(Rslt)
    If Len(Rslt) <= 1 Then
    Else
        If Left(Rslt, 1) = "-" Then nbrDigits = nbrDigits + 1
        If Abs(numericRslt) < 1 Then nbrDigits = nbrDigits + 1
        If InStr(1, Rslt, ".") > 0 Then nbrDigits = nbrDigits + 1
        End If
    NbrToSigDigits = Rslt & IIf(InStr(1, Rslt, ".") < 1, ".", "") _
        & String(.Max(0, nbrDigits - DigsInNbr), "0")
    If Right(NbrToSigDigits, 1) = "." Then _
        NbrToSigDigits = Left(NbrToSigDigits, Len(NbrToSigDigits) - 1)
        End With
    End Function

 

[BAlGaInTl]

Ok...
Which is (most) correct?

I guess that they are equally correct and incorrect if that makes any sense. They each made one error, but I would have to say that SF was the lesser of the two evils.


In Formula, C5 should be 12. The extra decimals would be a big no-no.

In SF, D11 should be 0.1200, although that is not as big a mistake as C5.

I'm going to try Tusharm's code now and see how that works.

 

[Aladin Akyurek]

Ok...
Which is (most) correct?

I guess that they are equally correct and incorrect if that makes any sense. They each made one error, but I would have to say that SF was the lesser of the two evils.


In Formula, C5 should be 12. The extra decimals would be a big no-no.

In SF, D11 should be 0.1200, although that is not as big a mistake as C5.

I'm going to try Tusharm's code now and see how that works.

Why do you let pass this occasion to clarify what kind of rule applies to figures like

12.345 (SigFigs = 2)
12.345 (SigFigs = 3)
128.567 (SigFigs = 2) ?

 

[BAlGaInTl]

Ok...
Which is (most) correct?

I guess that they are equally correct and incorrect if that makes any sense. They each made one error, but I would have to say that SF was the lesser of the two evils.


In Formula, C5 should be 12. The extra decimals would be a big no-no.

In SF, D11 should be 0.1200, although that is not as big a mistake as C5.

I'm going to try Tusharm's code now and see how that works.

Why do you let pass this occasion to clarify what kind of rule applies to figures like

12.345 (SigFigs = 2)
12.345 (SigFigs = 3)
128.567 (SigFigs = 2) ?

12.345 (SigFigs = 2) --> 12
12.345 (SigFigs = 3) --> 12.3
128.567 (SigFigs = 2)

The last one is a bit tougher. Technically the correct answer would have to be converted to scientific notation as 1.3E02 which is 2 sig figs. However, it would generally be accpeted to report the whole number of 130.

The more I think about the rules, the more I realize that it may be impossible to get excel to recognize and work with all possibilities. In my case, I'm going to be worried about numbers that are < 1 in most cases.

Tusharm's UDF works in all cases that I have come across to this point. It even reports 128.567 (2 SigFigs) as 130 which is an accepted answer. The only thing that you loose is the ability to keep scientific notation in your numbers. But, since I'm not really worried about that in my reports, I'm going to utilize that UDF.

I seem to remember a good training site that explained Sig Figs and Rounding if anyone is interested. Send me a message, or I can post it here when I find it.

Many thanks again to all who had input to this challenging problem.

 

[Aladin Akyurek]

Ok...
Which is (most) correct?

I guess that they are equally correct and incorrect if that makes any sense. They each made one error, but I would have to say that SF was the lesser of the two evils.


In Formula, C5 should be 12. The extra decimals would be a big no-no.

In SF, D11 should be 0.1200, although that is not as big a mistake as C5.

I'm going to try Tusharm's code now and see how that works.

Why do you let pass this occasion to clarify what kind of rule applies to figures like

12.345 (SigFigs = 2)
12.345 (SigFigs = 3)
128.567 (SigFigs = 2) ?

12.345 (SigFigs = 2) --> 12
12.345 (SigFigs = 3) --> 12.3
128.567 (SigFigs = 2)

The last one is a bit tougher. Technically the correct answer would have to be converted to scientific notation as 1.3E02 which is 2 sig figs. However, it would generally be accpeted to report the whole number of 130.

The more I think about the rules, the more I realize that it may be impossible to get excel to recognize and work with all possibilities. In my case, I'm going to be worried about numbers that are < 1 in most cases.

Tusharm's UDF works in all cases that I have come across to this point. It even reports 128.567 (2 SigFigs) as 130 which is an accepted answer. The only thing that you loose is the ability to keep scientific notation in your numbers. But, since I'm not really worried about that in my reports, I'm going to utilize that UDF.

I seem to remember a good training site that explained Sig Figs and Rounding if anyone is interested. Send me a message, or I can post it here when I find it.

Many thanks again to all who had input to this challenging problem.

Two observations:

1) Yes, you should use the UDF Tushar provided. It will be faster than a formula solution (at least faster than the last one I posted).
2) The last formula does handle any figure < 1.

Now that the specs are clear regarding figures > 1, the formula can be extended to cover them too, although I will not do so in the light what I stated under (1).

And, an advise if I may. Clear specs is essential. Giving examples with accompanying rules for computation (not Excel formulas) is even more so.

 

[BAlGaInTl]

Aladin, thank you for the input. I will try to be more descriptive in the future. I didn't realize this would be so difficult.

Tusharm,
I did some thorough testing of your UDF and found it works in almost all cases. The one problem that I ran in to was when trying to round a value less than one, where the value of the number rounding to was a zero.

For example
0.101 rounded to 2 sig figs comes up as 0.1 instead of 0.10

I'm still playing with the code that I started with, more out of fun than anything, but it also has one error that I can not figure out for the life of me. Maybe someone else can shed some light.

Function SF(Val As Variant, Fig As Variant) As Variant

    Dim x As Integer
    Dim k As Variant
    Dim i As Boolean
    Dim j As Boolean
    
    'Determine the number of decimal places to round for
    x = Fig - 1 - Int(WorksheetFunction.Log10(Abs(Val)))
    'Check to see if the last place to round is a zero
    i = Mid(Val, InStr(1, Val, ".") + x, 1) = "0"
    'Check the value of the number following the zero
    j = Int(Mid(Val, WorksheetFunction.Min(Len(Val), InStr(1, Val, ".") + x + 1), 1)) < 5
    'Calculate the number of decimal places in the value
    k = Len(Val) - WorksheetFunction.Find(".", Val)
    'If the number of desired decimals is greater than actual decimals, add the extra zeros
    If k < x Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), k + 1) & WorksheetFunction.Rept("0", x - k)
    'If the laster number to be rounded is 0 and the next number is less than 5 (round down)
    ElseIf i And j Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), x + 1)
    Else
        SF = WorksheetFunction.Round(Val, x)
    End If
End Function

It works for all instances except when x = -1. Other negative values work fine.

Example

1234.5 rounded to 4 sig figs (x = 0) comes out to 1235... great
1234.5 rounded to 2 sig figs (x = -2) gives the value 1200... super
1234.5 rounded to 1 sig fig (x = -3) gives the value 1000... terrific!
1234.5 rounded to 3 sig figs (x = -1) gives #VALUE!...(should be 1230)
wth?

I can't understand why -2 and -3 will work, but -1 will not?

 

[Aladin Akyurek]

Aladin, thank you for the input. I will try to be more descriptive in the future. I didn't realize this would be so difficult.

Tusharm,
I did some thorough testing of your UDF and found it works in almost all cases. The one problem that I ran in to was when trying to round a value less than one, where the value of the number rounding to was a zero.

For example
0.101 rounded to 2 sig figs comes up as 0.1 instead of 0.10

I'm still playing with the code that I started with, more out of fun than anything, but it also has one error that I can not figure out for the life of me. Maybe someone else can shed some light.

Function SF(Val As Variant, Fig As Variant) As Variant

    Dim x As Integer
    Dim k As Variant
    Dim i As Boolean
    Dim j As Boolean
    
    'Determine the number of decimal places to round for
    x = Fig - 1 - Int(WorksheetFunction.Log10(Abs(Val)))
    'Check to see if the last place to round is a zero
    i = Mid(Val, InStr(1, Val, ".") + x, 1) = "0"
    'Check the value of the number following the zero
    j = Int(Mid(Val, WorksheetFunction.Min(Len(Val), InStr(1, Val, ".") + x + 1), 1)) < 5
    'Calculate the number of decimal places in the value
    k = Len(Val) - WorksheetFunction.Find(".", Val)
    'If the number of desired decimals is greater than actual decimals, add the extra zeros
    If k < x Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), k + 1) & WorksheetFunction.Rept("0", x - k)
    'If the laster number to be rounded is 0 and the next number is less than 5 (round down)
    ElseIf i And j Then
        SF = Int(Val) & Mid(Val, InStr(1, Val, "."), x + 1)
    Else
        SF = WorksheetFunction.Round(Val, x)
    End If
End Function

It works for all instances except when x = -1. Other negative values work fine.

Example

1234.5 rounded to 4 sig figs (x = 0) comes out to 1235... great
1234.5 rounded to 2 sig figs (x = -2) gives the value 1200... super
1234.5 rounded to 1 sig fig (x = -3) gives the value 1000... terrific!
1234.5 rounded to 3 sig figs (x = -1) gives #VALUE!... wth?

I can't understand why -2 and -3 will work, but -1 will not?

=SIGN(A1)*SF(ABS(A1),...)