How do I show a number to a particular number of significant figures

Excel, despite its numerical bias, doesn't appear to have any way to present a number to some number of significant figures. All the rounding functions work on an absolute number of decimal places (or other significance); none of them on the relative basis needed for this. So here is a formula to do it, along with what I hope is a full explanation.


To get a number to n significant figures is simply a rounding exercise, so we use the ROUND function; this gives us ..

[purple][tt] ROUND(number, ...)[/tt][/purple]

(where the ... needs to be a number indicating how to round number).

Before we look at how to determine it, note that people normally people use rounding to round digits after the decimal point, e.g.

[purple][tt] ROUND(0.456,2) [/tt][/purple]rounds to 2 decimal places (the nearest multiple of 10[sup]-2[/sup]), giving 0.46
[purple][tt] ROUND(0.456,1) [/tt][/purple]rounds to 1 decimal place (the nearest multiple of 10[sup]-1[/sup]), giving 0.4

.. but it is also possible to use negative numbers to round before the decimal point, e.g.

[purple][tt] ROUND(456,[/tt][red][tt]-2[/tt][/red][tt])[/tt][/purple] rounds to hundreds (the nearest multiple of 10[sup]-(-2)[/sup] or 10[sup]2[/sup]), giving 500

Now, to round to a relative number of places depending on the number being rounded (i.e. n significant figures) requires that we first of all examine that number and determine how many digits it has. It would be possible to check its length but special case handling depending on whether or not the number contained a decimal point would make it quite complicated. An easier way is a mathematical one, using logarithms (the LOG10 Function).

A brief reminder from your schooldays: the logarithm of a (positive) number consists of two parts; the integer part which is the largest power of 10 lower than the number, and the decimal part indicating where the number falls in the range between that and the next highest power of 10. The integer part is what we are interested in here because it tells us where the first significant digit is, and for this we use the INT Function. An example ..

[purple][tt] LOG10(456) = 2.65896[/tt][/purple], and ..
[purple][tt] INT(LOG10(456)) = 2[/tt][/purple], telling us that the number falls between 10[sup]2[/sup] and 10[sup]3[/sup]

It is slightly more complex for numbers less than 1; the logarithm of 0.0456 is (mathematically) -2 +0.65896 and the integer part of this is -2. This doesn't appear quite so obviously from an example, but the INT Function moves away from zero so the result is what we want.

[purple][tt] LOG10(0.0456) = -1.34104[/tt][/purple], and ..
[purple][tt] INT(LOG10(0.0456)) = -2[/tt][/purple], telling us that the number falls between 10[sup]-2[/sup] and 10[sup]-1[/sup]

Adding 1 to the integer part of the log gives us the number of digits before the decimal point so, with the above examples ..

[purple][tt] INT(LOG10(456)) = 2[/tt][/purple]; add 1 and we find there are 3 digits before the point
[purple][tt] INT(LOG10(0.0456)) = -2[/tt][/purple]; add 1 and we find there are -1 digits before the point
[tt] [/tt](in other words there +1 zeroes after the point before we get to significant digits)

Now, do you remember the ROUND Function we started with? Rounding to n significant figures means rounding to the position n digits after the first significant figure, which is the position in the number relative to the decimal point (digits before it, or zeroes after it) which we got from the Log above, in other words ..

[purple][tt] INT(LOG10(number)+1) - n[/tt][/purple]

Because the ROUND Function requires the negative of this, what we actually feed into it is the reverse of the above ..

[purple][tt] n - (INT(LOG10(number)+1))[/tt][/purple]

Putting it together we get ..

[purple][tt] ROUND(number,n-(INT(LOG10(number)+1)))[/tt][/purple]

Finally, to cater for negative numbers, we ignore the sign in the process of determining the length. This is achieved by using the ABS Function, which gives the absolute value (the magnitude, or size) of a number. Adding this into the mix gives the final formula of ..

[blue][tt] ROUND(number,n-(INT(LOG10(ABS(number))+1)))[/tt][/blue]

So to find the value in, say, cell A1 to 4 significant figures, we can use this formula in another cell ..

[blue][tt] ROUND([red]A1[/red],[red]4[/red]-(INT(LOG10(ABS([red]A1[/red]))+1)))[/tt][/blue]