return zero from a blank cell
 

=E8-(B9+IF(ISBLANK(G8),0,G8))

I cant get this to work
If G8 is empty return 0 else return the value from G8 so that
the end result is E8 - B9 if G8 is empty ELSE E8-(B9+G8) if G8 has a value

Where did i go wrong?

Another problem:
E9 to E368 contains decreasing dollar values that eventually fall below
Zero. I want to find the first Exx cell at or below Zero and return the A
Cell from the same row (which is a date)
so for example,
H1 I1
Zero Date =Cells.Item(MATCH(E8,E368,-1), 1).Value

RowNum Col A Col E
335 Jun 1, 2003 $25.00
336 Jul 1, 2003 $15.35
337 Aug 1, 2003 -1.00
338 Sep 1, 2003 -16.00

so the formula would find Row 337 and return the Date in A:337 which is Aug
1, 2003 and Cell I1 would show that date

Thanks
Eric

Blank is zero, all you need is =E8-(B9+G8)

On the other

=MIN(IF(A1:A20<0,A1:A20))

which is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"Eric" wrote in message
...
=E8-(B9+IF(ISBLANK(G8),0,G8))

I cant get this to work
If G8 is empty return 0 else return the value from G8 so that
the end result is E8 - B9 if G8 is empty ELSE E8-(B9+G8) if G8 has a value

Where did i go wrong?

Another problem:
E9 to E368 contains decreasing dollar values that eventually fall below
Zero. I want to find the first Exx cell at or below Zero and return the A
Cell from the same row (which is a date)
so for example,
H1 I1
Zero Date =Cells.Item(MATCH(E8,E368,-1), 1).Value

RowNum Col A Col E
335 Jun 1, 2003 $25.00
336 Jul 1, 2003 $15.35
337 Aug 1, 2003 -1.00
338 Sep 1, 2003 -16.00

so the formula would find Row 337 and return the Date in A:337 which is
Aug
1, 2003 and Cell I1 would show that date

Thanks
Eric

Bob Phillips wrote:

Blank is zero, all you need is =E8-(B9+G8)

On the other

=MIN(IF(A1:A20<0,A1:A20))

which is an array formula, so commit with Ctrl-Shift-Enter.


=E8-(B9+G8)
compains that "this formula in this cell refers cells that are currently
empty" which is the same problem i had before and also the cell contains a
green triangle in its upper left corner. Whats that about?
sorry for basic q's but I'm real new to excel

=MIN(IF(A1:A20<0,A1:A20)) works great, thanks!
Eric

"Eric" wrote in message
...
Bob Phillips wrote:

Blank is zero, all you need is =E8-(B9+G8)

On the other

=MIN(IF(A1:A20<0,A1:A20))

which is an array formula, so commit with Ctrl-Shift-Enter.


=E8-(B9+G8)
compains that "this formula in this cell refers cells that are currently
empty" which is the same problem i had before and also the cell contains a
green triangle in its upper left corner. Whats that about?
sorry for basic q's but I'm real new to excel

Sorry, I have never seen that message.

Bob Phillips wrote:


"Eric" wrote in message
...
Bob Phillips wrote:

Blank is zero, all you need is =E8-(B9+G8)

On the other

=MIN(IF(A1:A20<0,A1:A20))

which is an array formula, so commit with Ctrl-Shift-Enter.


=E8-(B9+G8)
compains that "this formula in this cell refers cells that are currently
empty" which is the same problem i had before and also the cell contains
a green triangle in its upper left corner. Whats that about?
sorry for basic q's but I'm real new to excel

Sorry, I have never seen that message.

Ah, i found it, its an option that was turned on "Warn about formulas
referring to empty cells"
Anyway, thanks for the help, the stuff you showed me works good.
Eric

Where is that option? Excel version?

Bob