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

[JenniferMurphy]

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?

 

[mikerickson]

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]

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.

 

[JenniferMurphy]

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:

   =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]

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")

 

[Rick Rothstein]

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)...

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]

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]

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]

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:

   =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:

   =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]

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)...

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.