Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: formula needed to make a cell "blank" if 2 decimals are ".00

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    am trying to come up with a nested statement to make a cell result that shows two decimals as ".00" a blank. The problem is that the result is 6 decimals and has a calculated amount in the last two positions. This allows for it to round to".oo" in the cell,but, I need to make these cells "blank" for presentation purposes....this is a hyperion retrieve application so I can't just "wipe out" the cell.

    any help would be greatly appreciated and I would sprerad your good deeds to others!tx...

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could apply a Formula Is conditional format using the formula, =TRUNC(MOD(A2,1),2)=0, and set the font color to match the background cell pattern color. This would "hide" 0.002 and 100.00002, but display 0.01.

    I'm not quite sure if you want to "hide" 100.00002. If not use =TRUNC(A2,2)=0 instead.

    [ This Message was edited by: Mark W. on 2002-04-02 13:42 ]

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you need a vba statement, you could test to see if the cell content value is an integer:

    Sub Make_Int_Wte_Fnt()
    Dim myRng As Range
    Set myRng = [d9:j27] 'define the cell range here
    For Each Cell In myRng
    If Cell.Value = Int(Cell.Value) Then
    Cell.Font.ColorIndex = 2
    End If
    Next
    End Sub

    Or a formula:

    =IF(E13=INT(E13),"",E13)


    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-02 14:11 ]

Some videos you may like

User Tag List

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
  •