How to get excel to display a certain number of significant figures?

user_688463 0
Viewed 114509

I am using excel and i want to display a value to a certain number of significant figures.

I tried using the following equation

=ROUND(value,sigfigs-1-INT(LOG10(ABS(value))))

with value replaced by the number I am using and sigfigs replaced with the number of significant figures I want.

This formula works sometimes, but other times it doesn't.

For instance, the value 18.036, will change to 18, which has 2 significant figures. The way around this is to change the source formatting to retain 1 decimal place. But that can introduce an extra significant figure. For instance, if the result was 182 and then the decimal place made it change to 182.0, now I would have 4 sig figs instead of 3.

How do I get excel to set the number of sig figs for me so I don't have to figure it out manually?

spreadsheet significant-digits rounding excel
10 Answers

This is an old question, but I've modified sancho.s' VBA code so that it's a function that takes two arguments: 1) the number you want to display with appropriate sig figs (val), and 2) the number of sig figs (sigd). You can save this as an add-in function in excel for use as a normal function:

Public Function sigFig(val As Range, sigd As Range)
    Dim nint As Integer
    Dim nfrac As Integer
    Dim raisedPower As Double
    Dim roundVal As Double
    Dim fmtstr As String
    Dim fmtstrfrac As String

    nint = Int(Log(val) / Log(10)) + 1
    nfrac = sigd - nint

    raisedPower = 10 ^ (nint)
    roundVal = Round(val / raisedPower, sigd) * raisedPower

    If (sigd - nint) > 0 Then
        fmtstrfrac = "." & String(nfrac, "0")
    Else
        fmtstrfrac = ""
    End If

    If nint <= 0 Then
        fmtstr = String(1, "0") & fmtstrfrac
    Else
        fmtstr = String(nint, "0") & fmtstrfrac
    End If

    sigFig = Format(roundVal, fmtstr)
End Function

It seems to work in all the use cases I've tried so far.

user_26710090

As a very simple display measure, without having to use the rounding function, you can simply change the format of the number and remove 3 significant figures by adding a decimal point after the number.

I.e. #,###. would show the numbers in thousands. #,###.. shows the numbers in millions.

Hope this helps

user_92715160
Related