How do you tell Excel to round to a specific number of significant figures without having to use exponential notation? This Excel formula will do the trick:
Microsoft Excel is the industry leading spreadsheet software program, a powerful data visualization and analysis tool. Take your analytics to the next level with Excel. Microsoft Excel is one of the most versatile and useful programs in the Office suite. It doesn't matter if you need Excel templates for budgeting the next fiscal year, tracking your business inventory, planning out meals, or creating a fantasy football draft sheet, there are plenty of Microsoft Excel templates for you.
value :: the number you wish to round.
sigfigs :: the number of significant figures you want to round to.
There. Quick and easy. Continue reading below for more explanation about how this formula works, and visit my Excel Tips and Excel Templates pages for a lot of other great resources.
How the formula works:
The trick to this formula comes from understanding scientific notation. Reporting the number 12783 with three significant digits would give 1.28E4 or 1.28*10^4 or base*10^exponent.
- Excel is a powerful application—but it can also be very intimidating. That's why we've put together this beginner's guide to getting started with Excel. It will take you from the very beginning (opening a spreadsheet), through entering and working with data, and finish with saving and sharing.
- Email 252-247-4831 700 North 35th Street Morehead City, NC 28557. Email 252-247-1866 601 North 35th Street Morehead City, NC 28557. Email 910-325-0124 168 Queens Creek Road Swansboro, NC 28584. Email 910-577-3207 1 Office Park Drive.
- In the Settings tab of the pop-up menu, under Source, adjust the cell range to include the new cell you entered data into, or to exclude the one you deleted it from. Then click OK to save your.
Let's work backwards from what we want. We want to use the ROUND function for starters. But, we need to know the 'location' of the digit to round to. Remember that the way the ROUND function works in Excel, rounding 12783 to the 100s place means you use a 'location' of -2 or 12800=ROUND(12783,-2). If we want 3 significant digits, we just need to create a formula that gives -2 based upon the position of the first significant digit, or 1+exponent.
The formula for the exponent of 12783 is:
There we have it: 3 - (1 + 4) = -2
You can also use the ROUNDDOWN or ROUNDUP function in place of the ROUND function.
Custom Function for Rounding to Significant Figures
Syntax: ROUNDSIG(value,sigfigs)
Example: ROUNDSIG(-0.04589,2) equals -0.046
See Also: 'How to Create Custom Excel Functions'
Note: Trailing zeros on decimals numbers don't necessarily display correctly when using the above formulas. Although the value will be correct, Excel automatically formats a number with 5 sig figs such as 23.300 to display as 23.3 (unless the display format has been set to '0.000').
Working Around the Display Format Problem
To ensure that significant trailing zeros are displayed correctly when rounding a number to a certain number of significant digits, you need to work with text formats. The following formula is very confusing, but it gets the job done.
Update 4/19/2016: Fix for numbers such as 99.95 through 99.9999999 rounded to 3 SDs:
Excel 365 Login
Note: If you are okay with leaving the number displayed in a scientific notation (e.g. 0.100E+003 for the number 100), then the formula can be as simple as this:
Another Note: You can convert a text number to a value by multiplying by 1 or adding 0 or using double negation, for example:
I got the idea to use the text format in this way from John McGimpsey's site.However, the above megaformula lets you choose any number of significant digits. Below is a VBA function using this method to round significant digits. The function returns the value as a string, so when using the value in other formulas, you can use VALUE(cell) to convert the string to a numeric value.
How do you tell Excel to round to a specific number of significant figures without having to use exponential notation? This Excel formula will do the trick:
Microsoft Excel is the industry leading spreadsheet software program, a powerful data visualization and analysis tool. Take your analytics to the next level with Excel. Microsoft Excel is one of the most versatile and useful programs in the Office suite. It doesn't matter if you need Excel templates for budgeting the next fiscal year, tracking your business inventory, planning out meals, or creating a fantasy football draft sheet, there are plenty of Microsoft Excel templates for you.
value :: the number you wish to round.
sigfigs :: the number of significant figures you want to round to.
There. Quick and easy. Continue reading below for more explanation about how this formula works, and visit my Excel Tips and Excel Templates pages for a lot of other great resources.
How the formula works:
The trick to this formula comes from understanding scientific notation. Reporting the number 12783 with three significant digits would give 1.28E4 or 1.28*10^4 or base*10^exponent.
- Excel is a powerful application—but it can also be very intimidating. That's why we've put together this beginner's guide to getting started with Excel. It will take you from the very beginning (opening a spreadsheet), through entering and working with data, and finish with saving and sharing.
- Email 252-247-4831 700 North 35th Street Morehead City, NC 28557. Email 252-247-1866 601 North 35th Street Morehead City, NC 28557. Email 910-325-0124 168 Queens Creek Road Swansboro, NC 28584. Email 910-577-3207 1 Office Park Drive.
- In the Settings tab of the pop-up menu, under Source, adjust the cell range to include the new cell you entered data into, or to exclude the one you deleted it from. Then click OK to save your.
Let's work backwards from what we want. We want to use the ROUND function for starters. But, we need to know the 'location' of the digit to round to. Remember that the way the ROUND function works in Excel, rounding 12783 to the 100s place means you use a 'location' of -2 or 12800=ROUND(12783,-2). If we want 3 significant digits, we just need to create a formula that gives -2 based upon the position of the first significant digit, or 1+exponent.
The formula for the exponent of 12783 is:
There we have it: 3 - (1 + 4) = -2
You can also use the ROUNDDOWN or ROUNDUP function in place of the ROUND function.
Custom Function for Rounding to Significant Figures
Syntax: ROUNDSIG(value,sigfigs)
Example: ROUNDSIG(-0.04589,2) equals -0.046
See Also: 'How to Create Custom Excel Functions'
Note: Trailing zeros on decimals numbers don't necessarily display correctly when using the above formulas. Although the value will be correct, Excel automatically formats a number with 5 sig figs such as 23.300 to display as 23.3 (unless the display format has been set to '0.000').
Working Around the Display Format Problem
To ensure that significant trailing zeros are displayed correctly when rounding a number to a certain number of significant digits, you need to work with text formats. The following formula is very confusing, but it gets the job done.
Update 4/19/2016: Fix for numbers such as 99.95 through 99.9999999 rounded to 3 SDs:
Excel 365 Login
Note: If you are okay with leaving the number displayed in a scientific notation (e.g. 0.100E+003 for the number 100), then the formula can be as simple as this:
Another Note: You can convert a text number to a value by multiplying by 1 or adding 0 or using double negation, for example:
I got the idea to use the text format in this way from John McGimpsey's site.However, the above megaformula lets you choose any number of significant digits. Below is a VBA function using this method to round significant digits. The function returns the value as a string, so when using the value in other formulas, you can use VALUE(cell) to convert the string to a numeric value.
References
- Eric W. Weisstein. 'Significant Digits.' From MathWorld--A Wolfram Web Resource. https://mathworld.wolfram.com/SignificantDigits.html
- iSigSigma.com, 'Rounding Rules and Round-Off Rules for Statistical Data Analyses,' at http://www.isixsigma.com/library/content/t000813.asp
- 'Calculations with Significant Figures.' at http://www.mcgimpsey.com/excel/formulae/sigfigs.html
Cite This Article
To reference this article from your website or blog, please use something similar to the following citation:Wittwer, J.W., 'Rounding to Significant Figures in Excel' From Vertex42.com, October 28, 2004, https://www.vertex42.com/ExcelTips/significant-figures.html
Related Content
If you're an Excel beginner, this is the perfect place to start.
Microsoft Excel is one of the most used software applications of all time. Hundreds of millions of people around the world use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial, mathematical or statistical calculations.
1 Range: A range in Excel is a collection of two or more cells. This chapter gives an overview of some very important range operations.
Where Is My Excel On Windows 10
2 Formulas and Functions: A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.
