Help to modify a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Bob - 08 Feb 2008 03:55 GMT

This is my formula I need help with,
in cell I10 this is entered
=IF((I7-I6)*24>4,(I7-I6)*24-0.5,(I7-I6)*24)

Problem is when cell I6 & I7 are blank cell I10 shows #value I need it to be
blank or 0
any ideas?
thanks all.

Reply to this Message

Earl Kiosterud - 08 Feb 2008 04:11 GMT

Bob Bob,

You should be getting zero when I6 and I7 are empty. It sounds as if there's a space or
something like that in the cell. Some folks do that to clear a cell.

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeyl.com
-----------------------------------------------------------------------

> This is my formula I need help with,
> in cell I10 this is entered
[quoted text clipped - 4 lines]
> any ideas?
> thanks all.

Reply to this Message

joeu2004 - 08 Feb 2008 06:31 GMT

> This is my formula I need help with,
> in cell I10 this is entered
> =IF((I7-I6)*24>4,(I7-I6)*24-0.5,(I7-I6)*24)
> Problem is when cell I6 & I7 are blank cell I10 shows
> #value I need it to be blank or 0

You probably have "" or " " (some number of space characters) in I6 or
I7. If you have only empty cells (i.e. no formula or value) or "",
the following would suffice:

=if( or(I6="",I7=""), 0, if( (I7-I6)*24>4, (I7-I6)*24-0.5, (I7-
I6)*24 ) )

If you need to allow for " " as well, the following will work in all
cases, if zero is an acceptable result:

=if( n(I6)*n(I7) = 0, 0, if( (I7-I6)*24>4, (I7-I6)*24-0.5, (I7-
I6)*24 ) )

By the way, you might consider this simplification:

=if( n(I6)*n(I7) = 0, 0, (I7-I6)*24 - 0.5*( (I7-I6)*24 > 4 ) )

HTH.

Reply to this Message