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

Question

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?

Answer 1

The formula (A2 contains the value and B2 sigfigs)

=ROUND(A2/10^(INT(LOG10(A2))+1),B2)*10^(INT(LOG10(A2))+1)

may give you the number you want, say, in C2. But if the last digit is zero, then it will not be shown with a General format. You have then to apply a number format specific for that combination (value,sigfigs), and that is via VBA. The following should work. You have to pass three parameters (val,sigd,trg), trg is the target cell to format, where you already have the number you want.

Sub fmt(val As Range, sigd As Range, trg As Range)
    Dim fmtstr As String, fmtstrfrac As String
    Dim nint As Integer, nfrac As Integer
    nint = Int(Log(val) / Log(10)) + 1
    nfrac = sigd - nint
    If (sigd - nint) > 0 Then
      'fmtstrfrac = "." & WorksheetFunction.Rept("0", nfrac)
      fmtstrfrac = "." & String(nfrac, "0")
    Else
      fmtstrfrac = ""
    End If
    'fmtstr = WorksheetFunction.Rept("0", nint) & fmtstrfrac
    fmtstr = String(nint, "0") & fmtstrfrac
    trg.NumberFormat = fmtstr
End Sub

If you don't mind having a string instead of a number, then you can get the format string (in, say, D2) as

=REPT("0",INT(LOG10(A2))+1)&IF(B2-(INT(LOG10(A2))+1)>0,"."&REPT("0",B2-(INT(LOG10(A2))+1)),"")

(this replicates the VBA code) and then use (in, say, E2)

=TEXT(C2,D2).

where cell C2 still has the formula above. You may use cell E2 for visualization purposes, and the number obtained in C2 for other math, if needed.