Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop .00 but not .03 (Excel XP)

    I have a column of dollar amounts where the user wants us to Drop the .00 BUT if its .03 to leave it in. Don't want to round the number if its 9.03.

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop .00 but not .03 (Excel XP)

    Just found out one more thing.

    324.00 to 324
    343.23 to 34323

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Drop .00 but not .03 (Excel XP)

    Try the General Format, which shoul dmeet your needs. If you need to include the dollar sign use

    $ General

    with perhaps a trailing space or two.

    Andrew C

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Drop .00 but not .03 (Excel XP)

    In other words, show any fractional decimals, but not show decimals for integer numbers? (Sorry, I forget my math terminology.) Format, Cells, General does that.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    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: Drop .00 but not .03 (Excel XP)

    1) If your values are in A1 (change as appropriate) you could put this in another cell and display this TEXT:

    <pre>=TEXT(A1,IF(INT(A1)=A1,"$ 0","$ 0.00"))</pre>


    Then hide col A, the user only sees the "formatted text" values. If you want to use them in calcs use the values in col A.
    2) you could manually change the format of each cell

    3) create a macro to change them. Put this code in a module. Select the range of interest and run the macro.

    <pre>Option Explicit
    Sub ConvertFormat()
    Dim rCell As Range
    For Each rCell In Selection
    If rCell.Value = Int(rCell.Value) Then
    rCell.NumberFormat = "$#,##0"
    Else
    rCell.NumberFormat = "$#,##0.00"
    End If
    Next
    End Sub</pre>


    Change the format as desired.

    Steve

  6. #6
    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: Drop .00 but not .03 (Excel XP)

    How about this formula in another cell:
    <pre>=IF(INT(A1)=A1,a1,100*a1)</pre>


    Or modify my earlier macro to:

    <pre>Option Explicit
    Sub ConvertFormat()
    Dim rCell As Range
    For Each rCell In Selection
    If rCell.Value = Int(rCell.Value) Then
    Else
    rCell.value = 100* rcell.value
    End If
    Next
    End Sub</pre>


    Steve

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

    Re: Drop .00 but not .03 (Excel XP)

    Hi dreyes,

    Did you really mean to display 343.23 as 34323? Note that there's no decimal point in the latter.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop .00 but not .03 (Excel XP)

    I will try the formulas to day and see what happens. In the last post you asked if I wanted to display 343.23 as 34323... Yes, I did. Seems that the client wrote a program that will read the 34323 as 343.23 and 34300 as 343.00. Not sure why they did this, but they want it that way..

    As for the Format, General, $... I tried this one but it will round the number from 343.60 to 344. and like I said... the client wants in data formatted

Posting Permissions

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