# Is there a way to format number with variable decimal places?

#### JenniferMurphy

##### Well-known Member
I have a column of numbers that range from the thousandths (.nnn) to the thousands n,nnn. I'd like to format them with a variable number of decimal places according to this table:

 Value Decimal places <1 3 <10 2 <100 1 >=100 0

<tbody>
</tbody>

I only want to vary the appearance of the value, not the value itself.

I know I can use a nested IF statement with the Format function to get the desired formatting, but this changes the value in the cell. I can do the same thing with a UDF.

I know I can create a second column for the formatted value so the original value is preserved in the other column.

Is there another way so I can use a single column, vary the formatting, and not change the values?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### mikerickson

##### MrExcel MVP
Excel Custom Number Formats will only take 2 conditions, rather than your requested 3.
Would some variation of 3 cases, like
[<1]#.000;[<10]0.00;0.0

work for you

#### joeu2004

##### Banned user
I have a column of numbers that range from the thousandths (.nnn) to the thousands n,nnn. I'd like to format them with a variable number of decimal places according to this table:
 Value Decimal places <1 3 <10 2 <100 1 >=100 0

<tbody>
</tbody>

If you use XL2007 or later and you do not require XL2003 compatibility, you can use Conditional Formatting. Set up two rules:

Rule 1:
Formula: =(A1<100)
Format: Custom [<1]0.000;[<10]0.00;0.0

Rule 2:
Formula: =(A1>=100)
Format: Custom 0, or Number with 0 decimal places

Caveat: I don't know if there are potential adverse implications of having such CFs, especially for an entire column or large range. Generally, I try to minimize my use of CFs because sometimes they affect performance or workbook size adversely, IIRC.

Last edited:

#### JenniferMurphy

##### Well-known Member
Excel Custom Number Formats will only take 2 conditions, rather than your requested 3.
Would some variation of 3 cases, like
[<1]#.000;[<10]0.00;0.0

work for you

Ah, yes. I use it all the time for positive, negative, and zero, but forgot that it allows conditions in brackets.

Thanks for the reminder. It's not ideal, but better than the hassle of an extra hidden column or a custom UDF.

It's too bad that it is so restrictive (just 2 conditions). Would the programming work be so much more difficult to allow 3, or 5, or 100 conditions?

I have a similar complaint about the IF function. How hard would it be for them to provide a CASE statement:
Code:
``   =case(testcond, expr1, stmt1, expr2, stmt2, ...)``

That would be so much simpler and clearer than all the nested IF statements that come up so often. I guess the MSFT developers are too busy managing their stock portfolios to do any real development anymore. (sigh)

#### mikerickson

##### MrExcel MVP

=CHOOSE(A1, "one", "two", "three")

It can be altered. (note condition1 is a logical)

=CHOOSE(condition1+2*condition2+4*condition3+1,"none","c1","c2","c1+2","c3","c1+3","c2+3","c1+2+3")

Last edited:

#### Rick Rothstein

##### MrExcel MVP
The following event code will perform the formatting you want (change my Column A guess, marked in red below, to the actual column you need this for)...
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, Rng As Range, WholePart As Variant
If Intersect(Target, ActiveSheet.UsedRange) Is Nothing Then Exit Sub
If Intersect(Target, Columns("[COLOR=#ff0000][B]A[/B][/COLOR]")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Intersect(Intersect(Target, ActiveSheet.UsedRange), Columns("[COLOR=#ff0000][B]A[/B][/COLOR]")) Is Nothing Then Exit Sub
For Each Cell In Intersect(Intersect(Target, ActiveSheet.UsedRange), Columns("[COLOR=#ff0000][B]A[/B][/COLOR]"))
If Len(Cell.Value) > 0 And Application.IsNumber(Cell.Value) Then
WholePart = Int(Cell.Value)
If WholePart = 0 Then
Cell.NumberFormat = "0.000"
Else
Select Case Len(WholePart)
Case 1: Cell.NumberFormat = "0.00"
Case 2: Cell.NumberFormat = "0.0"
Case Else: Cell.NumberFormat = "0"
End Select
End If
End If
Next
Application.EnableEvents = True
End Sub``````

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

#### tusharm

##### MrExcel MVP
Not sure if supported in 2007 but check if you can use conditional formatting. I know 2010 and 2013 allow one to conditionally specify the number format. Just ensure you specify 'stop if true' for each c.f.

I have a column of numbers that range from the thousandths (.nnn) to the thousands n,nnn. I'd like to format them with a variable number of decimal places according to this table:

 Value Decimal places <1 3 <10 2 <100 1 >=100 0

<tbody>
</tbody>

I only want to vary the appearance of the value, not the value itself.

I know I can use a nested IF statement with the Format function to get the desired formatting, but this changes the value in the cell. I can do the same thing with a UDF.

I know I can create a second column for the formatted value so the original value is preserved in the other column.

Is there another way so I can use a single column, vary the formatting, and not change the values?

#### JenniferMurphy

##### Well-known Member
If you use XL2007 or later and you do not require XL2003 compatibility, you can use Conditional Formatting. Set up two rules:

Rule 1:
Formula: =(A1<100)
Format: Custom [<1]0.000;[<10]0.00;0.0

Rule 2:
Formula: =(A1>=100)
Format: Custom 0, or Number with 0 decimal places

Caveat: I don't know if there are potential adverse implications of having such CFs, especially for an entire column or large range. Generally, I try to minimize my use of CFs because sometimes they affect performance or workbook size adversely, IIRC.

I've fiddled around with CF a few times, but always found it awkward for what I was trying to do. I've also read that it can affect performance if applied over a range.

#### JenniferMurphy

##### Well-known Member

=CHOOSE(A1, "one", "two", "three")

It can be altered. (note condition1 is a logical)

=CHOOSE(condition1+2*condition2+4*condition3+1,"none","c1","c2","c1+2","c3","c1+3","c2+3","c1+2+3")

Wow, that's a very interesting function. Thanks for the pointer.

I am assuming that you intended something like this:

Code:
``   =choose((A1<1)+(A1<10)+(A1<100)+1,format(A1,"#,##0"),format(A1,"0.0"),format(A1,"0.00"),format(A1,"0.000"))``

This would all be so much simpler and clearer with my CASE statement:

Code:
``   =case(A1,"<1",format(A1,"0.000"),"<10",format(A1,"0.00"),"<100",format(A1,"0.0"),"",format(A1,"#,##0"))``

But both of these return a text value. I would really prefer to only change the formatting, not the value itself.

#### JenniferMurphy

##### Well-known Member
The following event code will perform the formatting you want (change my Column A guess, marked in red below, to the actual column you need this for)...
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range, Rng As Range, WholePart As Variant
If Intersect(Target, ActiveSheet.UsedRange) Is Nothing Then Exit Sub
If Intersect(Target, Columns("[COLOR=#ff0000][B]A[/B][/COLOR]")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Intersect(Intersect(Target, ActiveSheet.UsedRange), Columns("[COLOR=#ff0000][B]A[/B][/COLOR]")) Is Nothing Then Exit Sub
For Each Cell In Intersect(Intersect(Target, ActiveSheet.UsedRange), Columns("[COLOR=#ff0000][B]A[/B][/COLOR]"))
If Len(Cell.Value) > 0 And Application.IsNumber(Cell.Value) Then
WholePart = Int(Cell.Value)
If WholePart = 0 Then
Cell.NumberFormat = "0.000"
Else
Select Case Len(WholePart)
Case 1: Cell.NumberFormat = "0.00"
Case 2: Cell.NumberFormat = "0.0"
Case Else: Cell.NumberFormat = "0"
End Select
End If
End If
Next
Application.EnableEvents = True
End Sub``````

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thanks for that Rick. I've been meaning to dip my toe into event code procedures. This may be just the nudge I need. Thanks.

Replies
1
Views
47
Replies
9
Views
270
Replies
7
Views
88
Replies
15
Views
236
Replies
2
Views
224

1,191,196
Messages
5,985,227
Members
439,950
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back