Easy way to display non-zero decimal places?
Results 1 to 7 of 7

Thread: Easy way to display non-zero decimal places?

  1. #1
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    816
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Easy way to display non-zero decimal places?

    I have a UDF that calculates the odds for various events. One of the formats it can use for the result is the fractional odds (5:1, 3/4, etc.). I would like to display these in the most compact form possible. That is, no trailing zeroes. Here's some sample data the results I want:

    Actual Result Desired Display
    5.000/1.000 5/1
    2.500/1.000 2.5/1
    1.250/1.000 1.25/1
    0.667/1.000 0.667/1
    1.000/2.000 1/2
    1.000/1.250 1/1.25
    1.000/1.333 1/1.333

    My thought is to format it as "0.000" and then remove trailing zeroes. Is there a better way?
    Using Office 2007 Pro on Win XP Pro

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,365
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Easy way to display non-zero decimal places?

    Maybe ...

    A
    B
    C
    2
    0.5512
    1/1.81
    B2: =IF(A2 < 1, TEXT(1/A2, "1\/0.00"), TEXT(A2, "0.00\/1"))
    3
    1.4449
    1.44/1
    4
    1.0035
    1.00/1
    5
    0.5963
    1/1.68
    6
    0.8139
    1/1.23
    7
    0.85
    1/1.18
    8
    0.576
    1/1.74
    9
    0.4424
    1/2.26
    10
    2.9919
    2.99/1
    11
    0.4196
    1/2.38
    12
    0.5974
    1/1.67
    13
    2.7854
    2.79/1

  3. #3
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    816
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Easy way to display non-zero decimal places?

    Shg,

    I probably didn't make myself clear enough. The UDF has two values A & B (A"/"B). I want to format both numbers separately to remove trailing zeroes. I think your method does the division first, no?

    I just found this post from you from 2 years ago:

    https://www.mrexcel.com/forum/excel-...m-numbers.html

    This seems like a good solution, which I will apply to both values and then construct the ratio as a string.
    Using Office 2007 Pro on Win XP Pro

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,365
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Easy way to display non-zero decimal places?

    If you're happy, I'm happy.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,312
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Easy way to display non-zero decimal places?

    Quote Originally Posted by JenniferMurphy View Post
    I have a UDF that calculates the odds for various events. One of the formats it can use for the result is the fractional odds (5:1, 3/4, etc.). I would like to display these in the most compact form possible. That is, no trailing zeroes. Here's some sample data the results I want:

    Actual Result Desired Display
    5.000/1.000 5/1
    2.500/1.000 2.5/1
    1.250/1.000 1.25/1
    0.667/1.000 0.667/1
    1.000/2.000 1/2
    1.000/1.250 1/1.25
    1.000/1.333 1/1.333

    My thought is to format it as "0.000" and then remove trailing zeroes. Is there a better way?
    You did not post your UDF code, so it is not possible to tell how your odds are calculated, but if you calculate the numerator and denominator separately to the lowest form, then I would think this might work...

    Odds = Format(Numerator) & "/" & Format(Denominator)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    816
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Easy way to display non-zero decimal places?

    Quote Originally Posted by shg View Post
    If you're happy, I'm happy.
    Heh, well, you are easy to please.

    I was impressed by your clever solution in the other thread.
    Code:
    Function CCTrim(sInp As String) As String
       CCTrim = Replace(Trim(Replace(sInp, "0", " ")), " ", "0")
    End Function
    It took me a few minutes to figure out what you were doing. First you replace all of the zeroes with spaces, which includes embedded zeroes, then you remove (trim) leading and trailing spaces, then you replace the interior spaces with zeroes. It's very clever. I would not have thought of it in a million years. I love clever solutions like this. Thank you.
    Using Office 2007 Pro on Win XP Pro

  7. #7
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    816
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Easy way to display non-zero decimal places?

    Quote Originally Posted by Rick Rothstein View Post
    You did not post your UDF code, so it is not possible to tell how your odds are calculated, but if you calculate the numerator and denominator separately to the lowest form, then I would think this might work...

    Odds = Format(Numerator) & "/" & Format(Denominator)
    I see that I neglected to mention that I want no more than 3 decimal places. Otherwise, this would work great. Thanks.
    Using Office 2007 Pro on Win XP Pro

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com