×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

Best of Excel

How do I show a number to a particular number of significant figures by TonyJollans
Posted: 21 Apr 04


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 ..

    ROUND(number, ...)

  (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.

    ROUND(0.456,2) rounds to 2 decimal places (the nearest multiple of 10-2), giving 0.46
    ROUND(0.456,1) rounds to 1 decimal place (the nearest multiple of 10-1), giving 0.4

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

    ROUND(456,-2) rounds to hundreds (the nearest multiple of 10-(-2) or 102), 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 ..

        LOG10(456)  = 2.65896, and ..
    INT(LOG10(456)) = 2, telling us that the number falls between 102 and 103

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.

        LOG10(0.0456)  = -1.34104, and ..
    INT(LOG10(0.0456)) = -2, telling us that the number falls between 10-2 and 10-1

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

    INT(LOG10(456))    =  2; add 1 and we find there are 3 digits before the point
    INT(LOG10(0.0456)) = -2; add 1 and we find there are -1 digits before the point
                            (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 ..

    INT(LOG10(number)+1) - n

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

    n - (INT(LOG10(number)+1))

Putting it together we get ..

    ROUND(number,n-(INT(LOG10(number)+1)))

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 ..

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

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

    ROUND(A1,4-(INT(LOG10(ABS(A1))+1)))

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close