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

kristoj

New Member
Joined
Apr 1, 2002
Messages
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...
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-02 14:11
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top