Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post
    Is there some way to format numbers so they show a maximum of two decimal points. That is, 2 shows as "2", 2.1 shows as "2.1", 2.11 shows as "2.11" but 2.111 shows as "2.11". Just formatting to two decimal points does not work since 2 and 2.1 show as "2.00" and "2.10" respectively.


    Ronny
    Ronny Richardson

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Ronny - Try this on all of your numbers......
    =ROUND(A1,2)


    Tim

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    0.## as a custom number format will do that.
    The decimal point will be displayed, however, in all numbers...
    2 as 2.
    '--
    Jim Cone
    Portland, Oregon USA
    Special Sort add-in

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post
    0.## as a custom number format will do that.

    Very cool, just what I needed.

    Round will not work. It effects any formulas that reference the number plus =ROUND(2.1,2) displays as 2.10 when formatted to two decimal points; although, if you delete the formatting completely, round works if the number is not referenced in another formula.


    Ronny
    Ronny Richardson

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    There are features and drawbacks to each solution.

    Points to keep in mind ... if the numbers were 2.115 and 2.116, using 0.## as a format, the numbers will each "display" as 2.12.

    When added together it would "display" like this....
    2.12
    2.12
    -----
    2.23

    A user may expect to see 2.24 as the total.

Posting Permissions

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