Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I am subtracting two cells which contain time and the result is zero. but
when I am using conditional formatting to hide zero I can not. what can be the reason? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Rather than using conditional formatting, go to Tools|Options|View and untick
the "zero values" tick box. -- Traa Dy Liooar Jock "MV" wrote: I am subtracting two cells which contain time and the result is zero. but when I am using conditional formatting to hide zero I can not. what can be the reason? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If you mean the cell still shows 0:00 then I would think that the value is
not a true zero. Try re-formatting the cell as General and see if there is a small residual value in there. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "MV" wrote in message ... I am subtracting two cells which contain time and the result is zero. but when I am using conditional formatting to hide zero I can not. what can be the reason? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
What condition are you using in your conditional formatting?
One possibility (if your cell contents were the result of previous calculations) is that with the fixed point binary representation there may be rounding errors so that you have a resulting value close to zero but not identically zero, so it may be worth checking that. -- David Biddulph "MV" wrote in message ... I am subtracting two cells which contain time and the result is zero. but when I am using conditional formatting to hide zero I can not. what can be the reason? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Because of the way computers store decimal numbers (number to base 10) in
binary form (numbers to base 2) it often happens that a calculation that should yield exactly zero will actually result in a very small number like 0.000000000012. This only happens with real (that is, non-integer numbers) So it is advisable never to test for exactly zero but rather to test for 'smallness' =IF(ABS(A1-B1) < 1E-10, ..... =IF(ROUND(A1-B1, 10) =0, .... best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "MV" wrote in message ... I am subtracting two cells which contain time and the result is zero. but when I am using conditional formatting to hide zero I can not. what can be the reason? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Convert European Date format to American Format | Excel Discussion (Misc queries) | |||
Decide comment format 'globally'? Restore format with ws_change? | Excel Discussion (Misc queries) | |||
Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
how to format excel format to text format with separator "|" in s. | New Users to Excel |