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]

I was thinking more like
=TEXT(A1, REPLACE("#000", INT(LOG(A1))+3, 0, "."))

 

[tusharm]

LOL! You need better reasons to shy away from c.f. than these. But it's your work. So, if you find the alternatives proposed in this discussion easier to use and understand and "more efficient" then you should continue to stay away from c.f.s

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.

 

[joeu2004]

I've fiddled around with CF a few times, but always found it awkward for what I was trying to do.

Me, too; at least, the CF features introduced in XL2007.

I eschew those features by simply clicking on CF, New Rule, "Use a formula".

Then we're back to the simple pre-XL2007 interface: enter a formula which applies the format when true; and click on Format.

Tusharm makes a good point about selecting Stop If True. I believe we must click on Manage Rules to do that.

Regarding suggestions that rely on TEXT:

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

I think you were very clear about that requirement at the outset.

 

[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

This turned out to be the best solution. Thanks for reminding me.

BTW: It does take 3 conditions if you count the last one, which is "neither of the previous two".

 

[JenniferMurphy]

PS: I also wrote a little UDF to convert the number into a test string. It's not what I need for this application, but might be useful in another situation. Here it is in case anyone else might find it useful.

'======================================================================
'                          Convert Odds Function

' Convert the odds provided as a percentage into a "nnn.nn/1" format.

' Syntax: =CvtOdds(PCOdds)

'   PCOdds  The odds as a percentage
======================================================================
Function CvtOdds(PCOdds As Double) As String

Select Case PCOdds
  Case Is < 1
    CvtOdds = Format(PCOdds, "0.00")
  Case Is < 10
    CvtOdds = Format(PCOdds, "0.0")
  Case Is < 100
    CvtOdds = Format(PCOdds, "0")
  Case Else
    CvtOdds = Format(PCOdds, "#,##0")
End Select

CvtOdds = CvtOdds & "/1"

End Function

 

[Rick Rothstein]

PS: I also wrote a little UDF to convert the number into a test string. It's not what I need for this application, but might be useful in another situation. Here it is in case anyone else might find it useful.

'======================================================================
'                          Convert Odds Function

' Convert the odds provided as a percentage into a "nnn.nn/1" format.

' Syntax: =CvtOdds(PCOdds)

'   PCOdds  The odds as a percentage
======================================================================
Function CvtOdds(PCOdds As Double) As String

Select Case PCOdds
  Case Is < 1
    CvtOdds = Format(PCOdds, "0.00")
  Case Is < 10
    CvtOdds = Format(PCOdds, "0.0")
  Case Is < 100
    CvtOdds = Format(PCOdds, "0")
  Case Else
    CvtOdds = Format(PCOdds, "#,##0")
End Select

CvtOdds = CvtOdds & "/1"

End Function

If I am not mistaken, the following function should return the same values as your function above...

Function CvtOdds(PCOdds As Double) As String
  CvtOdds = Format(PCOdds, Left("#,##0.00", 8 + (PCOdds >= 1) + 2 * (PCOdds >= 10))) & "/1"
End Function

 

[JenniferMurphy]

If I am not mistaken, the following function should return the same values as your function above...

Function CvtOdds(PCOdds As Double) As String
  CvtOdds = Format(PCOdds, Left("#,##0.00", 8 + (PCOdds >= 1) + 2 * (PCOdds >= 10))) & "/1"
End Function

Wow. Are you a member of the infamous one-liner club?

It reminds me of an old IBM programming language called APL (A Programming Language). The code was so dense that they had competitions to see who could write the most complex programs in one line. One guy wrote a complete conversion from Arabic numbers to Roman numerals.

I tested your code and it seems to work. It's not so good for me because it's not immediately obvious to me what it does. If I were to decide to change the thresholds or the formats, it would take me awhile to figure out just what to do and I'd probably make mistakes. My code may not be as elegant, but I can change it in seconds.