Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Perform and Interpret Regression Analysis in Excel
  • Excel Bar Chart
  • How to combine 2 or more chart types in a single chart in Excel
  • Understanding Anova in Excel
  • Number and Text Filters Examples in Excel

References

  • How to get last row in numeric data in Excel
  • How to use Excel MMULT Function
  • Convert text string to valid reference in Excel using Indirect function
  • How to use Excel LOOKUP Function
  • How to get relative column numbers in a range in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation require unique number
  • Excel Data validation don’t exceed total
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells

Round a number to n significant digits in Excel

by

This tutorials shows how to Round a number to n significant digits in Excel.

If you need to round a number to a given (variable) number of specified digits or figures, you can do so with an elegant formula that uses the ROUND and LOG10 functions.

Formula

=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))

Explanation

In the example shown, the formula in D6 is as follows:

=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))

Note:
  1. ABS converts the value to an absolute (positive) value
  2. LOG10 gets the exponent, in this case 6 with a decimal value
  3. INT trims off the decimal part of the exponent
  4. The formula uses the exponent and the supplied significant digits to figure out the right number of digits to give ROUND
  5. ROUND rounds the number using the number of digits supplied
Worked Example:   Get age from birthday in Excel

How this formula works

This can be an intimidating formula if you don’t have a good background in math, but let’s work through it step-by-step.

First, when you have a formula like this where one function (in this case ROUND) wraps around all others, it’s often helpful to work from the outside in. So, at the core, this formula is rounding the value in B6 using the ROUND function:

=ROUND(B6,x)

Where x is the number of significant digits required. The tricky part of this formula is calculate  x.  This is a variable, because it will change depending on the number being rounded. x is calculated with this bit:

C6-(1+INT(LOG10(ABS(B6))))

This seems complicated, so let’s look first at how the formula needs to work for the given examples. Remember with ROUND that a negative number of digits works on the left side of the decimal. So, to round 1234567 to an increasing number of significant digits, we would have:

=ROUND(1234567,-6) = 1000000 // 1 sig. digit
=ROUND(1234567,-5) = 1200000 // 2 sig. digits
=ROUND(1234567,-4) = 1230000 // 3 sig. digits
=ROUND(1234567,-3) = 1235000 // 4 sig. digits

So, the main problem is how to calculate -6, -5, -4 and so on depending on the number we are rounding.

Worked Example:   Extract date from a date and time in Excel

The key is understanding how these numbers could be expressed using exponents, as in scientific notation:

=ROUND(1234567,-6) = 1000000 = 1.0*10^6
=ROUND(1234567,-5) = 1200000 = 1.2*10^6
=ROUND(1234567,-4) = 1230000 = 1.23*10^6
=ROUND(1234567,-3) = 1235000 = 1.235*10^6

Note that the exponent is 6 in all cases, which is determined with this bit:

INT(LOG10(ABS(B6)))

So, the rest of the formula is just using the calculated exponent value to figure out the right number to give ROUND depending on the number of significant digits desired:

=ROUND(1234567,-6) // 1-(1+6) = -6
=ROUND(1234567,-5) // 2-(1+6) = -5
=ROUND(1234567,-4) // 3-(1+6) = -4
=ROUND(1234567,-3) // 4-(1+6) = -3

 

Post navigation

Previous Post:

Number and Text Filters Examples in Excel

Next Post:

Convert date string to date time in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Invoice status with nested if in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel

Date Time

  • Display Date is same month in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • How to get Weekdays, Working days between Two Dates in Excel
  • How to calculate next anniversary date or birthday in Excel
  • Add months to date in Excel

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel

General

  • Count cells that do not contain errors in Excel
  • How to fill cell ranges with random text values in Excel
  • Count cells that contain errors in Excel
  • How to calculate profit margin percentage in Excel
  • Count cells less than in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning