TIP: How to conditional format number of decimal places

[cbrf23]

I was trying to figure out how to conditionally format how many decimal places to display, and I couldnt find an answer. I decided since I couldn't find an anwer online, I should share what I figured out.

I wanted at least 3 decimal places displayed always. But,if I typed more, I wanted more (up to 6).
Like so:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 width=64>INPUT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=64>DISPLAY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>1.000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num>10.000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1">.1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.1">.100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123">.123</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.123">.123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1234">.1234</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.1234">.1234</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.12345">.12345</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.12345">.12345</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123456">.123456</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num="0.123456">.123456</TD></TR></TBODY></TABLE>
To get this to work, you need to set the cell format as custom. And enter: .000###
This works like:

.000 '<--minimum decimal places
### '<--optional decimal places to display

You can get creative too.
For example:
[<1].0000######;#,###
works like:

    [<1] '<--If cell value <1 Then:
        .0000 '<--minimum decimal places
        ###### '<--optional decimal places to display
    ; '<--Else:
        #,### '<--no decimal places/ 0000, displayed as 0,000/ all digits optional

And gives you:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=64>INPUT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl26 width=64>ALT DISP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>10</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1">.1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.1">.1000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123">.123</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.123">.1230</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.1234">.1234</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.1234">.1234</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.12345">.12345</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.12345">.12345</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num="0.123456">.123456</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num="0.123456">.123456</TD></TR></TBODY></TABLE>

I found this really useful, and thought I would share since I had trouble finding an answer when I was searching.

 

[wigi]

Thanks for sharing.

But this does not mean that for instance:

5.123 will remain 5.123
while
5.123456 will remain 5.123456

or is it?

 

[cbrf23]

It does. Try it out

Using my example above of .000###:
If you enter .1, it will remail .1 in the formula bar, and is displayed as .001. The same as if you formatted as number and set your format to .000

If you enter .123 it is displayed as .123, and remains .123 in formula bar.

If you enter .123456 it is displayed as .123456, and remains .123456 in formula bar.

If you enter more than the optional characters... .1234567891011 would be displayed as .123456 but remains .1234567891011 in the formula bar.

Alternatively, using the other example of [<1].0000;#,###

if you entered .1234 it would be displayed as .1234

and if you entered 1.1234 or 1.123456 you would get 1

But you could use #,###.00 and get 1.12 in both cases
or #,###.00#### and get 1.1234 in both cases
or #,###.00###### and get 1.1234 in the first case, and 1.123456 in the second case (and 1.00 if you entered 1)

Does that answer your question?

 

[wigi]

5,123456

will be shown as

5,123

when the numberformat is

[<1].0000######;#,###

 

[cbrf23]

Try it out.
When I set format to [<1].0000;#,### I get the following:
<table border="0" cellpadding="0" cellspacing="0" width="184"><colgroup><col style="mso-width-source:userset;mso-width-alt:3584;width:74pt" width="98"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:74pt" height="20" width="98">INPUT</td> <td class="xl65" style="border-left:none;width:65pt" width="86">DISPLAY</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">123456</td> <td class="xl67" style="border-top:none;border-left:none">123,456</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">1234567</td> <td class="xl67" style="border-top:none;border-left:none">1,234,567</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">1,123456</td> <td class="xl67" style="border-top:none;border-left:none">1,123,456</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">0.1234</td> <td class="xl67" style="border-top:none">.1234</td> </tr> </tbody></table>
This works for me in both excel 2003 (work) and 2010(home)

 

[wigi]

But that's standard behavior, where's the "new" thing in your post?

You were talking about decimal places, and the numbers above (input) do not have decimal places...

(It also depends on regional settings, in Belgium decimal and thousand separators are reversed w.r.t. English/US settings.)

 

[mikerickson]

I can't figure this one out.
What custom format would display up to two decimal places, but not the decimal point if it isn't needed

12 >> "12"
12.3 >> "12.3"
12.34 >> "12.34"
12.346 >> "12.35"

 

[wigi]

Mike: that was my question too (phrased differently).

I thought the TS had a solution for this kind of problem.

 

[cbrf23]

Hmmmm....Well, in 2003 I'm not sure what you could do exactly.

I can't get it to drop the period on an integer either...

 

[Marcelo Branco]

Maybe with CF it can work. Something like:

1st rule
=ISNUMBER(SEARCH(".00",TEXT(A1,".00")))
format Button --> Number, 0(zero) decimal places

2nd rule
=AND(ISNUMBER(SEARCH(".?0",TEXT(A1,".00"))),ISERROR(SEARCH(",00",TEXT(A1,".00")))
format Button ---> Number 1 decimal place

For those using , (comma) as decimal separator and ; (semi-colon as separator) change to
1st rule
=ISNUMBER(SEARCH(",00";TEXT(A1;",00")))
Format button ---> Number 0 decimal places


2nd rule
=AND(ISNUMBER(SEARCH(",?0";TEXT(A1;",00")));ISERROR(SEARCH(",00";TEXT(A1;",00"))))
Format button ---> Number 1 decimal place

M.

Edit: first format A1 Number 2 decimal places