Return to Excel Formulas List

Circular Number to n Significant Figures in Excel & Google Sheets

Download Example Workbook

Download the example workbook

In this Commodity

  • What are Significant Figures?
  • Formula
  • ROUND Office
  • Number of Digits in Number
    • LOG10 Part
    • ABS Function
    • Number of Digits to Circular
  • Round Number to n Significant Figures in Google Sheets

This tutorial will demonstrate how to round a number to a specified number of significant digits in Excel & Google Sheets.

Significant MAIN

What are Significant Figures?

Significant figures are the number of digits that carry meaningful contributions to it'due south measurement resolution (source: Wikipedia).

Significant NEW

Formula

This is the formula to round a number to a specified number of digits (figures):

=Round(A2,B2-(1+INT(LOG10(ABS(A2)))))

This is a complicated formula. Let's simplify information technology:

=Round(number_to_round, sig_digits - (i + num_digits_in_number))

Below we will walk you lot through how the formula works.

ROUND Function

The ROUND Role rounds a number to specified number of digits relative to the decimal. By using negative numbers we can circular to the left of the decimal.

Round

As you can see, we a need a way to calculate the num_digits input in order to circular to a specified number of digits. Because nosotros know the significant digits that we want to round to, we simply demand a way to summate the number of digits in a number.

Number of Digits in Number

First let's calculate the number of digits in the number. We will practise this with the following formula:

=INT(LOG10(ABS(A2)))

No of Digits

Here is how the formula works. Numbers tin can be stored using scientific notation:

1234567 = i.234567 * 10 ^ 6  123456 = 1.23456 * 10 ^ v   12345 = one.2345 * 10 ^ 4    1234 = one.234 * 10 ^ 3     123 = 1.23 * 10 ^ 2      12 = ane.2 * 10 ^ 1

The exponent value in the scientific annotation tells us how many digits the number contains.

LOG10 Office

Side by side we tin can use the INT and LOG10 Functions to return the exponent from above.

LOG10

How does this work? The LOG10 function returns the exponent to which 10 must be raised to produce the number.

Nosotros use the INT Function to remove the decimal value from the exponent so simply the integer remains.

LOG10 and INT

ABS Office

LOG10 volition not work with negative numbers, so we also want to add together in the ABS Function to calculate the absolute value, giving us the formula to calculate the number of digits in a number.

LOG ABS

Number of Digits to Circular

Now nosotros know how to calculate the number of digits in a number. Recall our simplified formula above?

=ROUND(number_to_round, sig_digits - (1 + num_digits_in_number))

Plug in the formula to calculate the number of digits in a number:

=ROUND(number_to_round, sig_digits - (1 + INT(LOG10(ABS(number_to_round)))))

and let's add jail cell references to arrive at our formula:

=ROUND(A2,B2-(i+INT(LOG10(ABS(A2)))))

final

Round Number to northward Meaning Figures in Google Sheets

All of the in a higher place examples work exactly the same in Google Sheets as in Excel.

Significant Google