Short Answer
Rule Statement
This site is a precision and rounding reference, not just a calculator. Excel does not ship with a native SIGFIG function. The ROUND function rounds to a fixed number of decimal places, not to a number of significant figures. To make Excel respect significant-figure rules, you must convert the desired number of significant figures into the correct decimal place, then call ROUND.
For a value x in cell A2 and n significant figures, the custom formula is:
=IF(A2=0,0,ROUND(A2,n-1-INT(LOG10(ABS(A2)))))
The exponent term INT(LOG10(ABS(x))) gives the power of ten of the leading significant digit. The decimal-place argument d = n − 1 − INT(LOG10(ABS(x))) tells Excel where the last significant digit lies. For values less than 1, Excel’s INT correctly floors negative logarithms; TRUNC does not.
In Excel 365, wrap it as a LAMBDA and define a name such as SIGFIG: =LAMBDA(x,n,IF(x=0,0,ROUND(x,n-1-INT(LOG10(ABS(x)))))). Then use =SIGFIG(A2,3). Older versions require repeating the formula or a VBA user-defined function.
Worked Examples
The table below shows the decimal-place conversion and the resulting ROUND call. Remember that the numeric result may not display trailing zeros as significant unless you apply a custom format.
| Value x | n | d = n − 1 − INT(LOG10(ABS(x))) | Excel result | Correct sig-fig expression |
|---|---|---|---|---|
| 1234.567 | 3 | 3 − 1 − 3 = −1 | =ROUND(1234.567,−1) = 1230 | 1.23 × 10^3 |
| 0.0012345 | 3 | 3 − 1 − (−3) = 5 | =ROUND(0.0012345,5) = 0.00123 | 1.23 × 10^−3 |
| 9.99 | 2 | 2 − 1 − 0 = 1 | =ROUND(9.99,1) = 10.0 (numeric 10) | 1.0 × 10^1 |
| −1234 | 2 | 2 − 1 − 3 = −2 | =ROUND(−1234,−2) = −1200 | −1.2 × 10^3 |
| 0 | any | undefined | IF(0=0,0) = 0 | 0 (or 0.0 if n requires) |
Step-by-step for 1234.567 to 3 sig figs: The leading digit 1 is in the thousands place, so LOG10(1234.567) ≈ 3.0915. INT gives 3. d = 3 − 1 − 3 = −1. ROUND(1234.567,−1) rounds to the nearest ten, giving 1230.
Step-by-step for 0.0012345 to 3 sig figs: LOG10(0.0012345) ≈ −2.9084. INT gives −3. d = 3 − 1 − (−3) = 5. ROUND(0.0012345,5) gives 0.00123.
Counter-Examples
These common errors show why the custom formula must be built carefully.
- Using ROUND(A2,n): For 1234.567 with n=3, =ROUND(A2,3) returns 1234.567. It rounds to three decimal places, not to three significant figures. The correct answer is 1230.
- Using TRUNC for the exponent: For 0.0012345, TRUNC(LOG10(0.0012345)) = −2, so d = 4, giving 0.0012 (two significant figures). Correct uses INT = −3, d = 5, giving 0.00123.
- Omitting ABS: =LOG10(−1234) returns #NUM!, breaking the formula.
- Omitting zero handling: =LOG10(0) returns #NUM!.
- Assuming display equals significance: The formula returns numeric 10 for 9.99→2 sig figs; Excel General format shows 10, not 10. or 1.0E+01. Use scientific notation or a custom format to display the trailing zero.
- Using MROUND: =MROUND(1234.567,10^(n−1−INT(LOG10(ABS(A2))))) is not equivalent because MROUND rounds to a multiple, not to a decimal place. For n=3, the multiple is 0.1, so the result is 1234.6, not 1230.
Software Behavior Note
Excel stores numbers in IEEE 754 binary64. The custom formula works on the stored binary value, not on the displayed text. This matters in three ways.
- Round-half-away-from-zero: Excel’s ROUND rounds ties away from zero (e.g., 2.5→3, −2.5→−3). ISO 80000-1:2009 Clause 5.4.2 and ASTM E29-22 often use round-half-to-even for exact ties. If your discipline requires half-even, wrap the result in a conditional tie-breaker or use a VBA/LAMBDA routine that implements ASTM E29.
- Trailing zeros: Numeric values do not preserve trailing zeros. 10, 10.0, and 10.00 are the same number in Excel. To display sig figs, apply a custom number format, e.g., 0.00E+00 for three sig figs, or compute a text string.
- Floating-point artifacts: LOG10 near powers of ten can be slightly above or below an integer. For example, LOG10(1000) may evaluate to 3 or 2.9999999999999996 depending on the upstream calculation. In practice, the INT-based formula is robust for normal spreadsheet work, but for critical metrology, round the exponent or use a tolerance: =ROUND(n−1−INT(LOG10(ABS(A2))),0).
Quick Reference Table
| Task | Excel formula or setting | Notes |
|---|---|---|
| Round x to n sig figs | =IF(A2=0,0,ROUND(A2,n−1−INT(LOG10(ABS(A2))))) | Core custom formula. |
| Reusable LAMBDA | =LAMBDA(x,n,IF(x=0,0,ROUND(x,n−1−INT(LOG10(ABS(x)))))) | Define name SIGFIG in Excel 365. |
| Decimal places for n sig figs | d = n − 1 − INT(LOG10(ABS(x))) | Can be negative. |
| Handle zero | IF(x=0,0,…) | LOG10(0) is undefined. |
| Handle negatives | ABS(x) inside LOG10 | ROUND handles sign. |
| Display 3 sig figs | Custom format 0.00E+00 | Numeric value still lacks trailing zeros. |
| ASTM E29 half-even | Not native; use VBA or LAMBDA | Excel ROUND is half-away-from-zero. |
Common Mistakes
- Confusing significant figures with decimal places.
- Using ROUND(A2,n) instead of converting n to decimal places.
- Using TRUNC instead of INT for negative logarithms.
- Forgetting ABS for negative values.
- Forgetting the zero case, causing #NUM! errors.
- Expecting numeric formatting to change the stored value.
- Assuming Excel follows ASTM E29 or ISO 80000 tie rules. Excel’s ROUND does not.
- Double rounding: rounding intermediate results to sig figs before the final calculation. Carry guard digits, then round once at the end.
- Using MROUND as a shortcut for sig-fig rounding.
- Ignoring floating-point representation near powers of ten.
Standards Citation
- ISO 80000-1:2009, Quantities and units — Part 1: General, Clause 5.4.2 gives rules for rounding to a specified number of significant digits, including tie-to-even.
- ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, Section 6 provides rounding procedures for test data; ties are generally resolved to the nearest even digit.
- NIST SP 811 (2008), Guide for the Use of the International System of Units (SI), Section 5.4 discusses rounding and significant digits for SI values.
- JCGM 100:2008 (GUM), Evaluation of measurement data — Guide to the expression of uncertainty in measurement, Clause 7.2.2 recommends reporting uncertainty to one or two significant digits, which drives the final rounding of the measurand.
- NIST TN 1297 (1994), Guidelines for Evaluating and Expressing the Uncertainty of NIST Measurement Results, provides practical rounding guidance for uncertainty statements.
Related Rules
Significant-figure rounding is only one part of the precision toolkit. For addition and subtraction, the result is limited by decimal places; see Addition and Subtraction. For multiplication and division, the result is limited by the factor with the fewest significant figures; see Multiplication and Division. Exact numbers have infinite significance; see Exact Numbers. Scientific notation removes trailing-zero ambiguity; see Scientific Notation. Rounding methods such as half-up, half-even, and truncation are compared in Rounding Methods. For standards, see ASTM E29 and ISO 80000. For spreadsheet-specific notes, see Excel.
Sources & Further Reading
- ISO 80000-1:2009, Quantities and units — Part 1: General. Clause 5.4.2.
- ASTM E29-22, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications.
- NIST SP 811 (2008), Guide for the Use of the International System of Units (SI), Section 5.4.
- JCGM 100:2008 (GUM), Evaluation of measurement data — Guide to the expression of uncertainty in measurement, Clause 7.2.2.
- NIST TN 1297 (1994), Guidelines for Evaluating and Expressing the Uncertainty of NIST Measurement Results.
FAQ
Does Excel have a built-in SIGFIG function?
No. Excel's ROUND, ROUNDDOWN, ROUNDUP, and MROUND operate on decimal places or multiples, not significant figures. You must supply a custom formula or LAMBDA.
How do I round to 3 significant figures in Excel?
Use =IF(A2=0,0,ROUND(A2,3-1-INT(LOG10(ABS(A2))))). For a reusable function, define SIGFIG with LAMBDA.
Why does my result show 10 instead of 10.0?
Excel stores numeric values without trailing zeros. 10 and 10.0 are the same stored number. Apply a custom format such as 0.0 or 0.00E+00 to display the intended significant digits.
Does Excel's ROUND follow ASTM E29?
Not exactly. Excel's ROUND rounds half away from zero. ASTM E29 and ISO 80000-1 often use half-to-even for exact ties. For compliance, implement the tie rule explicitly.
How do I handle negative numbers?
Use ABS inside LOG10. The ROUND function handles the sign correctly. For example, -1234 to 2 sig figs becomes -1200.
Leave a Reply