Excel rounding of number

February 13, 2010 8:14 AM

Is there a way to force MS Excel to always give you four significant digits when rounding a number?

I'm formatting some output from Excel into a string and I want four significant digits. For example, if I have 5.76001 in Cell D2, I use the following formula:

="Number: " & ROUND(D2,4)

However, this gives me: "Number: 5.76", when I want to get "Number: 5.7600"

Any suggestions appreciated! Thanks.

posted by a womble is an active kind of sloth to Computers & Internet (6 answers total)

Use a custom number format that specifies 4 decimal places.

In XL 07, just go to the Home tab, select the drop-down menu in the Number box, select "more number formats," click on "Number" and then specify 4 decimal places.

Your 5.76 above will display as 5.7600.
posted by dfriedman at 8:16 AM on February 13, 2010 [1 favorite]

Note, however, if you use the ROUND function and specify as its argument a number of significant digits less than 4, that argument will override the general number setting.

So, if you follow the directions I give, and then enter in another cell =ROUND(A1,2), you will see only 2 significant digits.
posted by dfriedman at 8:18 AM on February 13, 2010

Response by poster: That doesn't appear to work for text + 'number as a string' concatenation.
posted by a womble is an active kind of sloth at 8:21 AM on February 13, 2010

Best answer: ="number: " &FIXED(D2,4)

seems to work = number: 5.7600
posted by selton at 8:25 AM on February 13, 2010

Right, because you're treating, say 1.2345 as a text string and not a number.

ROUND only works on numbers formatted as numbers.

You can confirm this by using the ISNUMBER function; it will return FALSE on what is returned.

To convert a number into a number and not text (wrap your head around that; it's vitally important in Excel), multiply it by 1: =ROUND("14.778828"*1,4)
posted by dfriedman at 8:25 AM on February 13, 2010

Response by poster: Thanks folks, fixed() did the trick!
posted by a womble is an active kind of sloth at 8:30 AM on February 13, 2010