Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Significant Figures

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello everyone.

We get lab data reports which have data in the form of

"<0.00657," or 4.2, 300, etc.

How do I create a spreadsheet which will automatically count the number of non- zero digits and display the correct significant figures?



View Answers     

Similar Excel Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics

I tried searching the subject to no avail.

Is there a worksheet function similar to round that will always round but outputs a specified amount of significant figures.

Cell A1 value = 0.0111
Cell A2 value = 0.00222

Both cells have 3 significant figures and I only need to display 2 significant figures so in theory they should display with rounding:

Cell A1: 0.011
Cell A2: 0.0022

If I use the function Round(A1,3) then Cell A1 displays 0.011 which is correct however if the value of A1 ever changes to 0.00333 or 0.00111 etc. then cell A1 will display the incorrect number of sig figs unless i changed the formula to Round(A1,4) but I don't want to be doing that all the time.

Hope this makes sense.

Working scientific field here and have a requirement to "report the result to 3 significant figures".

Our result is a mean [avg] and it will normally range from .05 to .199.

The quandry is how to format the cell to show "3 significant figures".

For example, the value ".0500" is considered to have 3 significant figures by this client's definition. Which would mean I have to format to at least 4 decimal places. However, if the calculated mean value is .1234, I'm only supposed to show 3 decimal places.

What's a programmer to do? Anyone else face this issue before?

I seem to have hit a limit of 16 significant digits. For example:
1E+15 + 1 = 1000000000000001 (16 significant digits)
(note that 1000000000000000 is displayed but the 1's place value is still
1E+16 + 1 = 10000000000000000.
The loss of significant digits can be verified by resubtracting the the
initial large number, e.i.:
1E+15 + 1 - 1+E15 = 1, whereas
1E+16 + 1 - 1+E16 = 0.
Is there a way of getting past this limitation? I need more than 40
significant digits for some rather special calculations. I have created a
workaround which cuts numbers into up to 3 15-significant-digit pieces, but
it's inelegant and requires very long formulas. Thanks.

Can excel cells be defined as a significant figure.

eg a cell containing the caluclation eg 55/3

= 18.333333333

i want that cell only to display 2 significant figures in this case 18

or if the calculation happened to give you an answer of 0.2458955435

then 2 signifcant figures would be 0.2


Can i set a range of cells to 3 significant figures automatically? I can only find it possible to do decimals places. Any ideas?


Hi Guys and Gals,

I would like to create a simple spreadsheet that will randomly generate numbers and then count the significant figures. I think the easiest way will be to generate the base random number, multiply by 10^(rand), convert to a string, and then count from the left or the right after hitting the first non-zero digit, but I need some help with the string functions.

Column: formula
A: =randbetween(1,999) /some integer 1 to 9999
B: =randbetween(-8,8) /this will be used to move the decimal
C: = A1*10^B1 /this will be the number my students will see
D: Ltrim(str(A1)) /this will convert to a string
E: =if(c1>0,"right","left) /start counting from left or right

Now I need to know how to count the digits after the first non-zero inclusive.

Example: 102405000 Start on the right and skip the zeros, but count from the 5 to the 1 (102405), and display 6 since there are 6 sig. figs.

0.0000230800 Start on the left and skip the zeros, but count from the 2 over to the last 0, and display 6 since there are 6 sig. figs.

Any suggestions? Anybody know of any programs that already do this?

Jeff Ciaccio
Sprayberry High School
Marietta, GA

In searching this site I found the following 2 formulas which I had hoped would be the solution to my problem.


Both work great in displaying an entered value of .981 as 0.98 (2 significant digits)

However, if a value of 1 is entered, I would expect these formulas to display 1.0 (2 significant digits).

I'm assuming the issue is Excel's ever-so-helpful removal of trailing zeros and can be overcome by custom formatting cells to avoid this. The problem I have is the user wants the option to vary significant digits as 2, 3 or 4. Even with custom formatting, I can't get these formulas to show more than 2 significant digits if a value of 1 [or any other whole number] is entered.

Any suggestions?

I am trying to digitize an ANSI standard that calls for specific rounding and
significant figures rules. For example, variable are specified as "7 sig
figs, simply rounded" or "4 places to right of decimal if pitch is greater
than 40, else 3 places."

I can work through the rounding, truncating, etc, but is there a way to
display these significant digits within a column using one formula? So far,
I've tried something like
"=IF(A12/$B$1<10,ROUND(A12/$B$1,6),ROUND(A12/$B$1,5))" but that won't account
for displaying. I tried conditional formatting, but that won't allow number

I want everything to be adaptable in case the main variables change (so I
can use a template sheet and copy them programmatically). Anyone have any



Does anybody know of a special format or a rounding function that displays or rounds a number off to a specific number of significant digits.

Lets say I would like 3 significant digits this would be the results:

0.40019 -> 0.400
6.543 -> 6.54
12.543 -> 12.5
32 -> 32.0
564.7894 -> 565
54387 -> 54400 (or 5.44E+4 to be correct, but i can live with 54400)

Is there a way to apply a custom format that will only display a certain
number of significant digits?

For instance for 3 significant digits
Input Values->Display As
0.12345->0.123 or .123
1234.5->1230 or 1234

I think I could make a formula that could do this, but I would rather keep
the precision. I just want to adjust the displayed precision.


I am trying to return values to a defined number of significant figures.

Bashing my brain and looking at the help I have come up with =round(Number,2-len(int(abs(Number))))

where Number is the cell reference with the number value.

However values such as 25 are returned as 25 instead of 25.0 and 0.0025 is returned as 0 instead of 0.00250.

Any help much appreciated.


Using Office 2007, "Duplicate Values" only works on identical numbers.

Is it possible to find values, which are identical to 4 significant figures, if there is a long list of data?

Many thanks!


I am wondering if anyone can help me with the following statement.

I have a number (example 1.068501) which i need to round up to 3 decimal places or 4 significant figures if the number is greater than 1 so the result would be 1.069.

If the number was (example 0.926145) then we need to round up to 4 decimal places or 4 significant figures so the result would be in this example would be 0.9262.

Can someone please provide me with a formula for this sort of calculation as I have a spreadsheet of over 500 numbers!

Cheers in advance

Hi all,

I have a macro that converts data in Excel to XML format. There are certain columns in Excel format that are far too accurate than necessary and I want to reduce the number of significant digits. While this is simple in Excel itself, when I run my macro to convert to XML, the original very long number is used.

Is there any way to use the Excel formatting to, for example, use a number to 3 significant digits rather than 9, 10 etc? Or is there relatively simple VBA code to reduce the number of decimal places in ceertain cells?

Here's my challenge: I want to automate a metric conversion (ounces to grams for instance) and round the result to the number of significant figures in the original number.

Example: 23 ounces converts to 652.039 grams -- rounded to two sigfigs would be 650.

If you can help me out I'd be most appreciative.

(If this has been covered already just point me to the link)

I need to know how to round to significant figures using excel, its for a question in one of my college assignments and i cant seem to find any information.. thankyou!

In my code, I'm calculating a very simple ratio and have the result displayed in a message box. However, I want to have this value only show 1 or 2 significant figures (e.g. if I take 9 / 7, I want to show 1.3 and not 1.28571).

How do I do this?

If I chart x and y values using the scatter option, fit a sixth-order polynomial, display the equation and then use the equation to re-calculate y values from the x values, the answers are totally different to the original data. I think this is a precision issue, and that if I could see the coefficients to more significant figures things might improve.

Does anyone know how to alter the numbers of significant figures in displayed equations or, alternatively, how to access the coefficients independently of the displayed equation? The curve itself fits very well, so the machine is obviously able to use its own equation effectively!

I have a formula that multplies a weekly hire rate by the number of days. This produces figures that have fractions of a penny that are not shown on the sheet. When I autosum these figures, it adds up all these fractions of a penny, and the resulting total does not then appear to agree.

Any suggestions for a quick fix gratefully received.

Thanks Pablo1


I want VBA code to control the number of signficant figures in a column. In column E2:E33, values will be entered in these cells from chemical test. However, I want the values to show only 3 sig figures. (ex. 2.534 will be 2.53 or 1.986 will be 1.99. etc...) How do I do this with code?

Thanks for any help.

I would appreciate some help to create the VBA code for a User Defined Function that will:

1) First, divide the number in a selected cell (or range of cells) by a constant number, 100
2) Next convert the format of the results into % to two significant figures
3) And finally align the % figures in the center.

E.g. If I have the number 12.23 in a cell, I would first divide it by 100, then format it into percentage in order to get 12.23%.

Thank you.

I am having problems figuring out how to auto update my table in the spreadsheet attached named "HST". Can anyone please explain the best way to link my 2 spreadsheets together. I have attached both spreadsheets. So, I have spreadsheet 1 which is called "HST" within which there are 2 table's displaying the held stock figures for 2 site's, both on the same worksheet. I then have spreadsheet 2 which is called "Weekly Figures", within which there are 52 worksheets, each worksheet is numbered 1 to 52 (I have had to condense this to just 5 worksheets for the up-load on this site due to the file size). At the end of each week, I go into the relevant week so for e.g, this week I've updated the held stock figures for week 1 in the "weekly figures" spreadsheet. This is the figures in the 2 tables - Table 1 is column C, D & E, rows 6 to 14 and also Table 2 which is again Column C, D & E, rows Rows 19 to 27. Once I have saved the data entered in the 2 tables within week 1 of the "Weekly Figures" spreadsheet, I would then like the data to display in the 2 tables within Spreadsheet 1 "HST" the next time the spreadsheet is opened. What would be the best way to do this.? Then the following week, I will then complete worksheet 2 within the "weekly figures" spreadsheet, with that weeks figures, after which, I would then like week 1 figures in the HST spreadsheet to overwite with week 2 figures the next time its opened. Does that make sense..?? I've attached both spreadsheets, if any one can help it would be appreciated & it would save me alot of work as this would be a function I would use in many other spreadsheets I have at work. Many Thanks Laura

Hi everybody!

I've made a macro in Excel/VBA, which I use to format numbers in Excel 2003 in such a way that the number is formatted with the number of decimals corresponding to a defined number of significant digits. For example: for 2 significant digits, the numbers 1234.56 and 0.56789 are formatted to 1200 and 0.57, respectively. It's important to stress that only the number format is changed, and not the values themselves, i.e. the values are not rounded down.

(And yes: I know that Excel 2007 can do this, but my workplace is stuck with 2003 for at least another couple of years... )

So far, so good... But what I want is a user-defined Excel function that does the same trick. For example, if my value is in cell A1, then I'd like to write in cell B1:


Where SIGDIG is the chosen number of significant digits. The value of B1 will be the same as A1, but with the right number format.

I've enclosed my macro code - if there's anyone out there that can help turn this macro into a user-defined Excel programme, I'd be eternally grateful.

Regards, Matthew A. Rasmussen

Sub FormatSigDig()

Dim value As Double
Dim decimals As Integer

value = Selection.value
significantdigits = 2

If value >= 9.95 Then
decimals = 0

Else: decimals = significantdigits - 1 - Int(Math.Log(Abs(Round(value, significantdigits - 1 - Int(Math.Log(Abs(value)) / Math.Log(10))))) / Math.Log(10))

End If

Select Case decimals
Case 0
Selection.NumberFormat = "0"
Case 1
Selection.NumberFormat = "0.0"
Case 2
Selection.NumberFormat = "0.00"
Case 3
Selection.NumberFormat = "0.000"
Case 4
Selection.NumberFormat = "0.0000"
Case 5
Selection.NumberFormat = "0.00000"
Case 6
Selection.NumberFormat = "0.000000"
Case 7
Selection.NumberFormat = "0.0000000"
Case Is > 7
Selection.NumberFormat = "0.0E+00"
End Select

End Sub