Explained clearly 4 min read

How to Round to Significant Digits in Google Sheets: A Formula That Works

Use =ROUND(A1, sig_figs - 1 - INT(LOG10(ABS(A1)))) to round a value to a chosen number of significant digits in Google Sheets, then format or convert to text to preserve trailing zeros.

Short Answer

Use =ROUND(A1, sig_figs - 1 - INT(LOG10(ABS(A1)))) to round a value to a chosen number of significant digits in Google Sheets, then format or convert to text to preserve trailing zeros.

Rule Statement

For a nonzero value x and a target of n significant digits, the Google Sheets formula is:

=ROUND(x, n – 1 – INT(LOG10(ABS(x))))

This formula works because LOG10(ABS(x)) returns the base-10 order of magnitude of the number. INT floors that value to an integer exponent. Subtracting that exponent from n – 1 converts significant digits into decimal places for ROUND. For example, the order of magnitude of 12345 is 4, so 3 significant digits require rounding to -2 decimal places, i.e. the hundreds place.

For zero, use a guard clause because LOG10(0) returns an error:

=IF(A1=0, 0, ROUND(A1, n – 1 – INT(LOG10(ABS(A1)))))

For blank or error-prone inputs, wrap the whole expression in IFERROR. The formula returns a numeric value. It does not preserve trailing zeros as significant. To display the rounded result with the correct number of significant digits, use TEXT or a custom number format. A scientific-notation display for n significant digits is:

=TEXT(ROUND(A1, n – 1 – INT(LOG10(ABS(A1)))), “0.” & REPT(“0”, n – 1) & “E+00”)

This site is a precision and rounding reference, not merely a calculator. The formula above is one implementation; the governing rules, conventions, and standards determine when rounding is appropriate and how ties should be resolved.

Worked Examples

Example 1: 12345 to 3 significant digits

LOG10(12345) = 4.091..., and INT(4.091...) = 4. The decimal-place argument is 3 - 1 - 4 = -2. ROUND(12345, -2) = 12300. Because 12300 has ambiguous trailing zeros, display it as 1.23E+04 or 1.23 × 10^4 to show exactly three significant digits.

Example 2: 0.004567 to 2 significant digits

LOG10(0.004567) = -2.340..., and INT(-2.340...) = -3. The decimal-place argument is 2 - 1 - (-3) = 4. ROUND(0.004567, 4) = 0.0046. Display as 4.6E-03 for two significant digits.

Example 3: 9.99 to 2 significant digits

LOG10(9.99) = 0.999..., and INT(0.999...) = 0. The decimal-place argument is 2 - 1 - 0 = 1. ROUND(9.99, 1) = 10 numerically. To show two significant digits, display 10. or 1.0E+01. Note that rounding can change the order of magnitude.

Example 4: -0.012345 to 4 significant digits

ABS(-0.012345) = 0.012345; LOG10(0.012345) = -1.908...; INT(-1.908...) = -2. The decimal-place argument is 4 - 1 - (-2) = 5. ROUND(-0.012345, 5) = -0.01235, which has four significant digits: 1, 2, 3, and 5.

Counter-Examples

Common errors arise from confusing decimal places with significant digits. For instance, ROUND(12345, 3) returns 12345, not 12300, because the second argument is decimal places. Another error is omitting ABS for negative numbers: LOG10(-0.012345) returns an error. Using TRUNC instead of INT also fails for values below 1. For 0.004567, TRUNC(LOG10(0.004567)) = -2, giving 2 - 1 - (-2) = 3 and ROUND(0.004567, 3) = 0.005, which has only one significant digit. Finally, forgetting the zero guard produces #NUM! because LOG10(0) is undefined.

Convention Comparison Table

Convention Tie-breaking rule Typical use Google Sheets ROUND
Half-up (commercial) 0.5 rounds away from zero General spreadsheet work Matches
Half-even (banker’s) 0.5 rounds to nearest even digit ASTM E29, some finance and statistics Does not match; custom formula needed
Half-down 0.5 rounds toward zero Rare; truncation-like behavior Does not match
Truncation Drop extra digits without rounding Conservative limits, some specifications Use TRUNC or INT

ASTM E29-22 and ISO 80000-1 recognize more than one rounding convention. The selected convention should be documented with the data. Google Sheets ROUND uses half-away-from-zero, so if a standard requires half-even, implement a custom tie-breaking rule or use a dedicated significant-figures calculator.

Standards Citation

ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, provides rules for rounding and significant digits in conformance testing. ISO 80000-1:2022, Quantities and units — Part 1: General, clause 6.2 addresses rounding and significant figures. NIST Special Publication 811, Guide for the Use of the International System of Units (SI), section 7.9 discusses rounding values. JCGM 100:2008, the Guide to the expression of uncertainty in measurement (GUM), clause 7.2.2 addresses rounding uncertainty. These standards emphasize that rounding is not merely arithmetic; it communicates precision, uncertainty, and conformance.

Common Mistakes

  • Using ROUND(x, n) and assuming n is significant digits.
  • Ignoring leading zeros and trailing-zero ambiguity.
  • Omitting ABS inside LOG10 for negative numbers.
  • Forgetting that zero requires special handling.
  • Assuming a numeric cell preserves trailing zeros as significant.
  • Double rounding: rounding intermediate results before the final calculation.
  • Using half-up when the governing standard requires half-even.
  • Not documenting the tie-breaking convention.

Software Behavior Note

Google Sheets ROUND(value, places) rounds half away from zero. LOG10 returns the base-10 logarithm. INT rounds down to the nearest integer toward negative infinity. ABS returns the absolute value. IFERROR handles zero, blank, and error inputs. Numeric cells do not retain trailing zeros; use TEXT or custom number formats. The scientific-notation display formula returns a text string, so further arithmetic requires VALUE. Google Sheets uses IEEE 754 double-precision floating point, which can produce edge cases near ties; see the related article on JavaScript floating-point for details.

Quick Reference Table

Input Sig figs Formula result Display for sig figs
12345 3 12300 1.23E+04
0.004567 2 0.0046 4.6E-03
9.99 2 10 10. or 1.0E+01
-0.012345 4 -0.01235 -1.235E-02
0 any 0 0 or 0.0 by context

FAQ

Why does ROUND(12345,3) not give 12300?

ROUND's second argument is decimal places, not significant digits. Use the LOG10 formula to convert significant digits into decimal places.

How do I keep trailing zeros in Google Sheets?

Use TEXT with a scientific-notation format or apply a custom number format. Numeric values in Sheets do not store trailing zeros as significant digits.

Does Google Sheets round half to even?

No. ROUND uses half-away-from-zero. If ASTM E29 or ISO 80000 requires half-even, implement a custom tie-breaking rule.

What about rounding zero to significant digits?

Zero has ambiguous significant figures. The formula returns 0 for zero; display it as 0, 0.0, or 0.00 according to context and the governing standard.

Verified sources

References

  1. ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, ASTM International.
  2. ISO 80000-1:2022, Quantities and units — Part 1: General, clause 6.2, International Organization for Standardization.
  3. NIST Special Publication 811, Guide for the Use of the International System of Units (SI), section 7.9, National Institute of Standards and Technology.
  4. JCGM 100:2008, Evaluation of measurement data — Guide to the expression of uncertainty in measurement (GUM), clause 7.2.2.
  5. Google Sheets function documentation for ROUND, LOG10, INT, ABS, IFERROR, and TEXT, Google Workspace Help.

Leave a Reply

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