Hiding Zero Cells

[Dave Palmer]

Hi

I have used the Tools>Options>view and unchecked the Zero
Values box to hide all zero cells in my worksheet,
however, certain calculations on 'Time formatted' cells
that return a zero value (not all) still show 0:00.

Any ideas please?

Dave

 

[Guest]

Just some thoughts...
My guess is that the offending cells really contain values
other than 0, in spite of appearances. You could change
the format of the offending cells to Number with 15
decimal places (highest stored precision in Excel) to see
if there are any stray bits out in the far decimal places.

Calculations using decimal values, time included, always
result in numbers that may have annoying rounding error at
the end (.000000000000001, .999999999999999). They might
display as zero way up in the higher decimal places, but,
like most folks, deep down they really want to "amount to
something"!

If you're calculating values that you can anticipate
should come out to zero or are so close to zero that they
should be zero, then you might have to do the calculation
in an IF() with an option to force a zero result (e.g.: =IF
(calcresult<somesmallnumber,0,calcresult)).

Time value rounding can be difficult because things are in
ratios of 60's, and those calculations can easily produce
infinitely repeating decimals.

Good luck.

 

[Guest]

If my previous ideas are close, then your IF should say
something like =IF(ABS(calcresult)
<somesmallnumber,0,calcresult). This would cover the case
of stray bits in small negative residuals as well as the
positive ones.

 

[Jonathan Rynd]

Time value rounding can be difficult because things are in
ratios of 60's, and those calculations can easily produce
infinitely repeating decimals.

Actually date/time serial numbers use 1 = day, 1/24 = hour, 1/1440 =
minute, 1/86400 = 1 second.

 

[Guest]

-----Original Message-----


Actually date/time serial numbers use 1 = day, 1/24 = hour, 1/1440 =
minute, 1/86400 = 1 second.
.

Jonathan,
I didn't realize that. I was looking at the ratios
1/1440:1/24 and 1/86400:1/1440 in thinking about rounding
in the seconds and minutes places. Maybe the original
problem could be better solved by manipulating
calculations using the SECOND(), MINUTE() and HOUR()
functions.
Thanks for the feedback.

 

[Dave Palmer]

Hi

Thanks very much for that. Spot on! I actually had to show
23 decimal places before I saw any values. Who said
computers were accurate?

Dave