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

kristoj · Apr 2nd, 2002, 02:34 PM

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...

Mark W. · Apr 2nd, 2002, 02:41 PM

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 ]

NateO · Apr 2nd, 2002, 03:03 PM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 02:34 PM	#1
kristoj New Member Join Date: Apr 2002 Posts: 1	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...

Apr 2nd, 2002, 02:41 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Apr 2nd, 2002, 03:03 PM	#3
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]