How to Round to Significant Figures in Excel (ROUND + LOG10 Method)

Rounding to a specified number of significant figures is a fundamental skill in science, engineering, and metrology. While Excel offers a built-in ROUND function, it rounds to a fixed number of decimal places, not significant digits. To round to significant figures, you need to combine ROUND with LOG10 to determine the number’s magnitude. This article provides a definitive reference for implementing this method correctly, adhering to international standards, and avoiding common errors.

Rule Statement

The general formula to round a number to n significant figures in Excel is:

=ROUND(number, n - 1 - INT(LOG10(ABS(number))))

Here’s how it works:

• ABS(number) ensures the logarithm works for negative numbers.
• LOG10(ABS(number)) gives the base-10 logarithm, which indicates the order of magnitude.
• INT() truncates the logarithm to an integer, giving the exponent of the leading digit.
• n – 1 – INT(…) computes the number of decimal places needed so that only n digits remain significant.
• ROUND(number, decimal_places) applies standard rounding (half away from zero in Excel).

For example, to round 12345 to 3 significant figures: =ROUND(12345, 3-1-INT(LOG10(12345))) → =ROUND(12345, 2-4) → =ROUND(12345, -2) → 12300.

This formula works for all non-zero numbers. For zero, the result is zero regardless of n. For numbers between 0 and 1, the logarithm is negative, and the formula correctly shifts the decimal point.

Worked Examples

Example 1: Rounding 0.004567 to 2 significant figures

1. Calculate LOG10(ABS(0.004567)) ≈ -2.340
2. Take INT: -3 (since INT rounds down to the more negative integer)
3. Compute decimal places: 2 – 1 – (-3) = 4
4. Apply ROUND: =ROUND(0.004567, 4) → 0.0046

Result: 0.0046 (2 significant figures).

Example 2: Rounding 9876500 to 4 significant figures

1. LOG10(9876500) ≈ 6.994
2. INT = 6
3. Decimal places = 4 – 1 – 6 = -3
4. =ROUND(9876500, -3) → 9877000 (since 6500 rounds up)

Result: 9.877 × 10⁶ (or 9877000).

Example 3: Rounding -2.345 to 3 significant figures

1. ABS(-2.345) = 2.345, LOG10 ≈ 0.370
2. INT = 0
3. Decimal places = 3 – 1 – 0 = 2
4. =ROUND(-2.345, 2) → -2.35 (Excel rounds half away from zero)

Result: -2.35.

Counter-Examples

Common mistakes arise from misusing ROUND or misinterpreting the formula.

Counter-Example 1: Using ROUND with a fixed number of decimals

=ROUND(12345, 2) gives 12345.00, which still has 5 significant figures. This does not round to a specific number of significant figures.

Counter-Example 2: Forgetting the -1 in the exponent

If you use =ROUND(number, n - INT(LOG10(ABS(number)))), you will get one extra significant figure. For 12345 with n=3, this gives =ROUND(12345, 3-4) → =ROUND(12345, -1) → 12350, which has 4 significant figures.

Counter-Example 3: Using LOG10 on zero

LOG10(0) returns an error. The formula must handle zero separately: if the number is 0, return 0. Also, for very small numbers, INT(LOG10(…)) can be misleading; test with 0.000123.

Convention Comparison Table

Different rounding conventions affect the result when the digit to be dropped is exactly 5. Excel uses round half away from zero (also called symmetric rounding). Other conventions include:

When working to standards like ASTM E29, the preferred method is often “round half up” or “round half to even,” depending on the field. Excel’s default is half away from zero, which may not match your discipline’s requirement.

Standards Citation

Several standards govern rounding and significant figures. The formula and its interpretation align with these:

• ASTM E29-08 – Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications. Section 6.1.1 defines the “rounding method” and Section 6.2.1 specifies that the retained digits should be increased by one if the discarded portion is greater than half a unit, and unchanged if less than half. For exactly half, it recommends rounding to the nearest even digit (Section 6.2.2).
• ISO 80000-1:2009 – Quantities and units – Part 1: General. Annex C provides rules for rounding, including the use of significant figures.
• NIST SP 811 – Guide for the Use of the International System of Units (SI). Section 7.9 discusses rounding and significant figures, advising that rounding should be done at the end of calculations.
• GUM (JCGM 100:2008) – Evaluation of measurement data – Guide to the expression of uncertainty in measurement. Section 7.2.6 addresses rounding of measurement results and uncertainties.

These standards emphasize that the rounding rule must be stated explicitly and that intermediate rounding should be avoided.

Common Mistakes

• Applying the formula to zero – Use an IF statement: =IF(A1=0, 0, ROUND(A1, n-1-INT(LOG10(ABS(A1))))).
• Forgetting that LOG10 of numbers less than 1 yields negative values – This is handled correctly by INT, but test with numbers like 0.000123.
• Using ROUNDUP or ROUNDDOWN instead of ROUND – These do not follow standard rounding rules.
• Not considering negative numbers – Always use ABS inside LOG10, but keep the original sign in the ROUND argument.
• Assuming trailing zeros are not significant – When rounding to a specific number of significant figures, trailing zeros after the decimal point are significant and must be displayed. For example, rounding 1.234 to 3 sig figs gives 1.23, but rounding 1.230 to 3 sig figs gives 1.23 (the zero is not significant if it is not retained). However, if the original number is 1.2300, rounding to 3 sig figs gives 1.23, but to 4 sig figs gives 1.230.
• Using scientific notation incorrectly – The formula works with numbers in any format, but the result may be displayed in scientific notation if the cell format is set to that. Ensure the cell format matches the desired precision.

Practice Problems

1. Round 0.003456 to 3 significant figures using the formula.
2. Round 123456 to 2 significant figures.
3. Round -987.65 to 4 significant figures.
4. Round 100.1 to 2 significant figures.

Answers: 1) 0.00346, 2) 120000 (or 1.2×10⁵), 3) -987.7, 4) 100 (but note that 100 has ambiguous significant figures; better to use 1.0×10²).

Software Behavior Note

Excel’s ROUND function uses round half away from zero. This differs from Python’s round() (banker’s rounding) and from some statistical software that use round half to even. If your discipline requires a different tie-breaking rule, you may need to implement a custom VBA function or use an alternative formula with conditional logic. For example, to implement round half to even in Excel, you would need to detect the exact half case and adjust accordingly.

Also, Excel stores numbers in double-precision floating-point format, which can introduce tiny errors (e.g., 0.1 + 0.2 ≠ 0.3 exactly). This can affect the rounding of numbers very close to a tie. For critical applications, consider using the ROUND function on the original value, not on a calculated result that may have floating-point artifacts.

Quick Reference Table