Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    numeric formatting without decimal point (XL2000)

    Is it possible to format "floating point" numbers with up to two decimal places, but omit the decimal point when no decimal digits are present? The closest approach that I have found is format code 0.##;0 which gives me

    3.83 as 3.83
    0.00 as 0
    72.00 as 72. [but what I want is 72]
    0.60 as 0.6

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric formatting without decimal point (XL2000)

    I can't see how to do this as a cell format but if you want it for a calculated field you may be able to use the text() function to format it:

    =IF(MOD(D19,1),TEXT(D19,"0.##"),D19)

    This does have the effect of rounding out the decimal places if used for further calculations though!

    HTH

    Peter

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: numeric formatting without decimal point (XL2000)

    Even worse: if the number has decimals, this formula will return a text value. Functions like SUM will ignore text values, but formulas with + won't, so you can get unexpected discrepancies in calculations...

    But fortunately, this needn't be a problem at all! Use the original values for further calculations and the formatted values for display only. If desired, the original values can be hidden.

    So your solution works very well after all. I like it, for I couldn't think of a number format to accomplish it.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric formatting without decimal point (XL2000)

    I had not noticed that functions would not use it <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Still it made me look again <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    It looks as if I did it the hard way anyway!

    =ROUND(D20,2) seems to be a better function

    Peter

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: numeric formatting without decimal point (XL2000)

    Who wants simple solutions when you can have complicated ones? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    OK, Round is much better, but you may still want to use the original values for further calculations, because you lose precision using Round - unless that is what you want, of course.

    Regards,
    Hans

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: numeric formatting without decimal point (XL2000)

    The format code that you want is General. See attached worksheet. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: numeric formatting without decimal point (XL2000)

    Sam,

    Mike asked for a format
    <hr>with up to two decimal places<hr>
    If a number has more than 2 decimal places, your Text function will display them instead of limiting to 2.

    Oh, and "General" causes problems in international use too: it is language dependent, so in my Dutch language version of Excel I got error values.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: numeric formatting without decimal point (XL2000)

    Mike,
    If your data is in column A, starting at row 1, the following formula in any other column, and copied down as many rows as you require it, should produce what you've specified:
    =ROUND(A1,IF(LEN(MOD(A1,1))>1,2,0))
    Cheers

    PS: As noted in other posts here, truncating your data at 2 decimal places could affect calculations. If that is a concern, or if you don't want to have extra columns with these calculations on your data worksheet, consider creating another worksheet with the required output formatting for printing and using the above formula to link to the source data on the original worksheet
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: numeric formatting without decimal point (XL2000)

    This method still will depend on the formatting of the cell no matter what the rounding.
    I do not believe there is a way to do what was asked simply. There is no way in EXCEL to create a truly CUSTOM format.
    You either:
    have to go the "convert" route change the number to a display that "looks" correct, but you must tack on VALUE before any calculation using it
    Or
    You must have a macro format the cell based on the contents which means it must run constantly whenever calculations are done. it could be set to only change particular cells, but your performance will start to drop dramatically.

    If the "values" are only for some "presentation" or display, I would just keep the real numbers somewhere and set up a "display" table which does not use numbers but the TEXT set up to LOOK like what you want and never use those tabled numbers in calculations, only use the original values which have NOT been touched.

    Steve

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: numeric formatting without decimal point (XL2000)

    Steve,

    Have you actually tried my solution? It displays the results exactly as originally specified. It also certainly does not require VALUE to be tacked on before using the results in any other calculation.

    Further, since Mike was talking about floating point numbers, they're most likely produced via formulae. In that case, replacing the A1 references in my formula with the formulae used to generate the floating point numbers would eliminate the need for intermediate cells.

    The cost of using the rounded results, as I acknowledged in my previous post, is that the rounded-off values could affect subsequent calculations. That is why I also suggested having the required output on a seprate worksheet, with my formula referencing the unrounded data.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: numeric formatting without decimal point (XL2000)

    Yes, it does MOST of what he wanted. But he did NOT want a rounded value. He wanted to DISPLAY the values but still have all the figures (non-diplayed) for calculations so he doesn't lose his precision. I do NOT think all that he wants is possible in excel.

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric formatting without decimal point (XL2000)

    I cannot see that

    =ROUND(A1,IF(LEN(MOD(A1,1))>1,2,0))

    gives any different result to

    =ROUND(A1,2)

    Unless there is some variation caues by international setting <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Peter

  13. #13
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric formatting without decimal point (XL2000)

    Gentlemen,

    Thank you for your suggestions. I was hoping that Excel could handle what I wanted directly, but (as Steve pointed out) it appears that it doesn't. However, I adapted Peter's suggestion to set the format based on the results of "casting out ones." This appears to give the look that I want (at least on the test data that I'm using <img src=/S/grin.gif border=0 alt=grin width=15 height=15>). I had to write my own function (dModD) to perform the modulo division on Doubles, since the VBA mod operator and CLng() function both round round floating point inputs, instead of truncating them. That coding was pretty straightforward, though.

    The discussion of side effects on the ability to do calculations was also very informative. It hadn't occurred to me that one could get a useful numerical result by "adding" text strings. After playing with it, I was even more surprised that it gave the correct answer when SUM() wouldn't! <img src=/S/shocked.gif border=0 alt=shocked width=15 height=15>

    <pre>'if value is within 1 min. of an hour then format without decimal point
    If dModD(Cell.Value, 1) < 0.167 Or dModD(Cell.Value, 1) > 0.983 Then
    Cell.NumberFormat = "0"
    Else
    Cell.NumberFormat = "0.##"
    End If
    </pre>


    Another interesting aspect of this discussion is what I left out of my question (because I didn't realize that it was relevant <img src=/S/blush.gif border=0 alt=blush width=15 height=15>). My worksheet is generated by VBA code, using data that were generated by another program. The worksheet's purpose is to format/present and in some cases to graph that data, so calculations aren't necessary. My user is in the US, so the international issues won't matter. It's always good to know the limitations of pieces of code, though, because somehow they seem to get re-used.

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric formatting without decimal point (XL2000)

    Should

    If dModD(Cell.Value, 1) < 0.167 Or dModD(Cell.Value, 1) > 0.983 Then

    really be

    If dModD(Cell.Value, 1) < <span style="background-color: #FFFF00; color: #000000; font-weight: bold">0.017</span hi> Or dModD(Cell.Value, 1) > 0.983 Then

    ??

    Peter

  15. #15
    Lounger
    Join Date
    Aug 2002
    Location
    Boise, Idaho, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: numeric formatting without decimal point (XL2000)

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    You are absolutely correct. So somebondy read my code after all! Thanks.

Posting Permissions

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