CODEWITHSUNDEEP
excel
Leo
Leo

Reputation: 67

Function that returns number of significant figures

I have no idea on how to make a function on excel that returns the number of significant figures in a number. So for example. If I enter 0.0001004 the output would be 4.

Upvotes: 3

Views: 5956

Answers (3)

ProfessorFluffy
ProfessorFluffy

Reputation: 391

This should work for any number. It is pretty complex due to the many rules governing sig figs. If you want to use trailing zeros after the decimal, just set your input cell to Text format.

=IF(B3="","",IF(ABS(B3)=0,0,LEN((ABS(B3)/10^LEN(ABS(B3)))*10^(LEN(ABS(B3)/10^LEN(ABS(B3)))-LEN(INT(ABS(B3)/10^LEN(ABS(B3))))-1)))+LEN(B3)-LEN(ABS(B3))-IF(LEFT(B3,1)="-",1,0)+IF(OR(LEFT(B3,1)=".",LEFT(B3,2)="-."),1,0))+IF(AND(NOT(ISERROR(FIND(".",B3))),ISERROR(FIND(".",ABS(B3)))),-1,0)

This equation is a bit more complicated than absolutely required due to it's ability to handle text input where you get complicating factors like 0.123 vs .123, negative numbers, and trailing zeros (e.g. -.0900).

enter image description here

Upvotes: 1

zx8754
zx8754

Reputation: 56149

I think you are looking for:

=IF(AND(A1<1,A1>0),LEN(MID(A1,IF(ROUND(-LOG10(A1),0)=0,3,ROUND(-LOG10(A1),0)+2),99)),0)

First IF checks if the input number is in 0-1 range, ROUND(-LOG10(A1),0) finds the number of zeros, the MID substring from non-zero digit to the end(99), LEN check the length.

enter image description here

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

This small UDF will display the number of significant digits displayed in the cell after the decimal point:

Public Function Sig(r As Range) As Long
    If InStr(r.Text, ".") = 0 Then
        Sig = 0
        Exit Function
    End If

    t = Split(r.Text, ".")(1)
    l = Len(t)
    For i = 1 To l
        If Left(t, 1) = "0" Then
            t = Mid(t, 2)
        Else
            Exit For
        End If
    Next i
    Sig = Len(t)
End Function

It is clearly dependent on the formatting of the cell

Upvotes: 0

Related Questions