Excel – How to format Excel numbers so that >= 0.1 has 1 significant digit, but < 0.1 has two significant digits

microsoft excel

I'm trying to figure out how to format numbers in Excel to display as follows:
If the number is >= 0.1, there should be one digit after the decimal place, rounded up, but if it's < 0.1, there should be two digits after the decimal place.

So, for example:

entered | displayed
--------+----------
0.08      0.08
0.1       0.1
0.17      0.2
30.33     30.3

I tried entering various types of number formatting:

[<0.1]#.##,#.#
[<0.1]#.0#,#.#

However, these don't correctly display the data the way I want. How can I display the data as desired?

Best Answer

You can achieve this by using the below custom number format:

[<0.1]0.00;[>=0.1]0.0

You were more or less there

You had entered the < criteria and format

You just needed to specify >= part

Related Question