1

My Excel spreadsheet is calculating some decimal values. Depending on the input value, the result may be e.g.

0
0.5
0.04166(6)

I would like these values to appear as

0
0.5
0.0417

that is limit the maximum number of decimal digits but not set the minimum one, allowing it to be 0 or more as needed.

With "general" format, I get the minimum required but unlimited number of digits, i.e. 0, 0.5, 0.0416667.
With "number" format, I get 0.0000 and 0.5000 if I want to get 0.0417.

Any way around? FWIW I am on Excel 2010.

2
  • 1
    Why would trailing zeros be a problem? A value of 0.0000 would be equal to 0. The same for 0.5 and 0.500. Right now there doesn't seem to be an easy way to do this. Of course you could always use the ROUND function and not use a specific format.
    – Seth
    May 26, 2017 at 10:15
  • @Seth because of readability. In my case 0 means there's no data. Redundant digits increase "noise".
    – texnic
    May 27, 2017 at 7:00

2 Answers 2

4

In case, if you need to keep the values not rounded, try custom format 0.####:

enter image description here

This is just one extra point off (in 0.) from the described format.

2
  • This modifies the value. There are easier ways to do that, and it's not what he wanted
    – Aganju
    May 26, 2017 at 17:32
  • @g.kov: I know you already edited the answer, however, I'd just recommend removing the first part you mention using TEXT function ... just keep it using the format as you put in your edit . that's the correct answer ...
    – Ditto
    May 26, 2017 at 18:48
2

If changing the value is not an issue for you (you wrote 'appear as'), you can use =ROUND(...) around whatever you calculate, or in a new column. The second parameter defines to how many digits you want it rounded, for example
=ROUND(2/3,4) will give you 0.6667.

1
  • In principle, rounding would be fine. So it was good to receive this option to consider. However I prefer changing the format rather than the formula in each column.
    – texnic
    May 28, 2017 at 19:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .