Help to modify a formula

  • Thread starter Thread starter Bob Bob
  • Start date Start date
B

Bob Bob

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.
 
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.
 
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
Click to expand...

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.
 
Back
Top