Doug,

This is true (forgot that). But it is a clean zero and won't affect some
calculations the way
"" in a cell can.

Also - if you don't want to display 0 you can always set Tools Options to
not display zero values.

This gets around the problem of values less than 0.01 from displaying +/- 0
values.

Just my 0.001 cents worth...

--
steveB

Remove "AYN" from email to respond
"Doug Glancy" wrote in message
...
Steve,

If I use an If as you describe with the False argument not specified, I
get a zero in the cell.

Doug

"STEVE BELL" wrote in message
news:fRaLe.1312$Xw5.910@trnddc02...
2 things that help me:

instead of looking for 0, I look for <0.01

another is in the If
If(x=y,z,)

notice the blank after z,
this tends to leave the cell nicely blank in appearance and in value.

--
steveB

Remove "AYN" from email to respond
"Doug Glancy" wrote in message
...
Whew, glad I could help!

Doug

"StargateFanFromWork" wrote in message
...
<g Ah ... dunceville ... :oD

Okay, tried it again and, yessss! It does work now. I again changed
the
last digit of "2" to "5" and the values then show up correctly (in my
very
last post I mention that the values were off by a few minutes until I
changed that "2" to "5". Hope that's okay to do.)

Looking good so far. Going to do some more tests now.

Thanks!!!


"Doug Glancy" wrote in message
...
SFFW,

The code that I showed in the last post was just a snippet that you
would
substitute into the larger formula:


=IF(E3<"",IF(ROUND(F4-SUM(D3-C3)-TIME(7,30,0),2)=0,"",ROUND(F4-SUM(D3-C3)-T
IME(7,30,0),2)),"")

hth,

Doug

"StargateFanFromWork" wrote in message
...
Okay, I _think_ I understand. I _do_ have to fix this -0 value
after
all
so
good thing I mentioned it. I'm guessing that once I do, that
secondary
formula will work (?). Okay. Unfortunately, the doubles correction
formula
you provide below isn't working either <damn. I added a "=" in
front
of
the "IF(Round..." because without it, the entire formula is all that
is
displayed in the cell rather than any numerical result. But after
adding
that "=", I get this error:

"Your formula is missing a parenthesis--) or (. Check the formula,
and
then
add the parenthesis in the appropriate place."

Phew, this one is a challenge ...

Meantime, thanks for all the help! I learn so much every time I
work on
a
problem in Excel. :oD


"Doug Glancy" wrote in message
...
SFFW,

I forgot to mention that, as Andi said earlier in this thread, the
values
are probably .0000001 or something so they don't evaluate to zero.
You
sometimes get these types of errors in Excel (and other programs).
It
has
to do with the way doubles are calculated or something like that.
Try
googling "double precision error excel". To fix your formula, you
could
round the numbers you're testing:

IF(Round(F4-SUM(D3-C3)-TIME(7,30,0),2)=0, etc.

which tells it to round to 2 decimals.

hth,

Doug

"StargateFanFromWork" wrote in message
...
Hi!

Well, that certainly fixed the error. That nested IF seems to
have
been
needed. Unfortunately, the additions didn't work as all "0"
values
still
showing (?). Hmmm ...

btw, weirdness since I copy/pasted but I noticed that initial
formula
I
posted has a small incorrect value that I can't explain as I
didn't
type
in
the values. The F4 below should be F3, as can logically be seen
in
hindsight <g. Still, that didn't affect the end result. Even
once
fixed,
all values are still being displayed instead of just the ones
resulting
numbers other than "0". Tx.


"Doug Glancy" wrote in message
...
SFFW,

I believe Andi's formula is meant to have a nested "If:" I
didn't
reproduce
your spreadsheet so I'm not sure, but this might be what you're
looking
for:




=IF(E3<"",IF(F4-SUM(D3-C3)-TIME(7,30,0)=0,"",F4-SUM(D3-C3)-TIME(7,30,0)),""
)

hth,

Doug

"StargateFanFromWork" wrote in message
...
I actually mentioned the -0 in case it might make a difference
to
the
formula in keeping column G empty unless value is not 0
(whether
negative
or
not <g). Do you think I can just leave it as is? The
workbook
seems
to
work just fine despite this the negative and positive values.

Re the second, thanks for the formula! It didn't work,
however.
About
the
only thing I figured out to do is to fix the word wrap. I
read
all
the
paragraphs in help as prompted under "I have an error in my
formula."
but
was unable to figure out what is wrong.

What to do?

Thanks! :oD

"Andibevan" wrote in message
...
This reason for negative zeros may be something to do with
rounding -
i.e.
although it says -0.00 it may actually be -0.00000001 -- Try
copying
the
cell and doing paste special values and then increase the
decimal
places.

I think this is likely to be the reason as it is
mathematically
impossible
to have a negative zero and I presume that Excel is governed
by
this
mathematical principal as well.

With regards to your second problem try this in cell G3





=If((E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")=0,"",(E3<"",F4-SUM(D3-C3)-TIME(7
,30,0),""))

HTH

Andi


"StargateFanFromWork" wrote in
message
...
I have a great spreadsheet that's really been a life-saver.
Just
one
thing
I'd like to fine-tune. In column G, specifically in cell
G3, I
have
the
following code:

=IF(E3<"",F4-SUM(D3-C3)-TIME(7,30,0),"")

Before I ask my question, just want to note that results
are
funny
in
one
way: to show "negative" hours in results, I changed to the
1904
system
for
this workbook as per advice from this ng. So that works
beautifully.
When
less time is worked during day, the total regular hours
worked
show
as
a
negative which is then taken into account in the
"overtime".
The
actual
overtime worked, then, shows "real" overtime worked and not
just
a
result
based on the fact that I worked till such-and-such an hour.

However, the odd thing is that sometimes that column
displays
as
0h00m
and
sometimes it shows up as -0h00m. I haven't figured out why
it
sometimes
puts that negative sing in. Ultimately, the mathematical
end
result
of
"0"
is the same, of course, but that makes this column awkward.
It
was
this
in
part that prompted me to wish this formula displayed
results
differently.

Since this is a visual check calculation only in this cell
so
that
I
can
see
if a workday has been worked in its entirety, it actually
would
be
better
if
the above code only displayed for any other value other
than
"0".
Is
there
a way, then, to incorporate that into the above formula?
i.e.,
that
the
cell will be also be completely blank unless there is a
resulting
value
other than 0h00m or -0h00m?

Thanks much! :oD