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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
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:

ValueDecimal places
<13
<102
<1001
>=1000

<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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
<13
<102
<1001
>=1000

<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:
Upvote 0
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)
 
Upvote 0
How about the CHOOSE function

=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:
Upvote 0
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.
 
Upvote 0
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:

ValueDecimal places
<13
<102
<1001
>=1000

<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?
 
Upvote 0
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.
 
Upvote 0
How about the CHOOSE function

=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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top