Function Error?

[Jackson]

Hi,

I'm really new to excel and am having a hard time trying to complete an
assignment for my teacher. Could anyone tell me what is wrong with this
formula?

=IF(AND(ISBLANK(F6)),(ISBLANK(G6)),"", (H6+G6-F6))

What I want to do is in a simple accounting worksheet calculate the data
in two individual cells to complete a running balance in a third cell,
but if the first two are empty, I want the third cell to be blank, too.

Thanks.

 

[joeu2004]

Could anyone tell me what is wrong with this formula?
=IF(AND(ISBLANK(F6)),(ISBLANK(G6)),"", (H6+G6-F6))

Duh, you tell us! ;-)

If you are referring to the syntax error, the problem is some extraneous
parentheses. You probably want:

=IF(AND(ISBLANK(F6),ISBLANK(G6)),"", (H6+G6-F6))

However, you also have some unnecessary parentheses. And I would recommend
that you test for "blank" as follows:

=IF(AND(F6="",G6""),"",H6+G6-F6)

ISBLANK() is true only if the cell is truly empty; that is, no constant and
no formula.

But suppose F6 or G6 has a formula like the above, which might return the
null string (""). Then ISBLANK() returns false even though the cell appears
to be blank.

F6="" is true when the cell is truly empty __and__ when the cell contains
the null string. It's a "good practice" even if you are sure it will not
contain the null string.

(There are times when you want to make a distinction between truly empty and
containing the null string. That is when you want to use the ISBLANK
function.)

What I want to do is in a simple accounting worksheet calculate the data
in two individual cells to complete a running balance in a third cell, but
if the first two are empty, I want the third cell to be blank, too.

If this formula goes into H6, I'll bet you want:

=IF(AND(F6="",G6""),"",H5+G6-F6)

That does assume that H5, G6 and F6 do not return the null string. Probably
a reasonable assumption. But if they might, the following avoids any
problem:

=IF(AND(F6="",G6""),"",SUM(H5,G6)-N(F6))

 

[Jackson]

Duh, you tell us! ;-)

If you are referring to the syntax error, the problem is some extraneous
parentheses. You probably want:

=IF(AND(ISBLANK(F6),ISBLANK(G6)),"", (H6+G6-F6))

However, you also have some unnecessary parentheses. And I would
recommend that you test for "blank" as follows:

=IF(AND(F6="",G6""),"",H6+G6-F6)

ISBLANK() is true only if the cell is truly empty; that is, no constant
and no formula.

But suppose F6 or G6 has a formula like the above, which might return
the null string (""). Then ISBLANK() returns false even though the cell
appears to be blank.

F6="" is true when the cell is truly empty __and__ when the cell
contains the null string. It's a "good practice" even if you are sure
it will not contain the null string.

(There are times when you want to make a distinction between truly empty
and containing the null string. That is when you want to use the
ISBLANK function.)



If this formula goes into H6, I'll bet you want:

=IF(AND(F6="",G6""),"",H5+G6-F6)

That does assume that H5, G6 and F6 do not return the null string.
Probably a reasonable assumption. But if they might, the following
avoids any problem:

=IF(AND(F6="",G6""),"",SUM(H5,G6)-N(F6))

Thank you, joeu!

 

[Don Guillett]

Duh, you tell us! ;-)

If you are referring to the syntax error, the problem is some extraneous
parentheses. You probably want:

=IF(AND(ISBLANK(F6),ISBLANK(G6)),"", (H6+G6-F6))

However, you also have some unnecessary parentheses.  And I would recommend
that you test for "blank" as follows:

=IF(AND(F6="",G6""),"",H6+G6-F6)

ISBLANK() is true only if the cell is truly empty; that is, no constant and
no formula.

But suppose F6 or G6 has a formula like the above, which might return the
null string ("").  Then ISBLANK() returns false even though the cell appears
to be blank.

F6="" is true when the cell is truly empty __and__ when the cell contains
the null string.  It's a "good practice" even if you are sure it will not
contain the null string.

(There are times when you want to make a distinction between truly empty and
containing the null string.  That is when you want to use the ISBLANK
function.)


If this formula goes into H6, I'll bet you want:

=IF(AND(F6="",G6""),"",H5+G6-F6)

That does assume that H5, G6 and F6 do not return the null string.  Probably
a reasonable assumption.  But if they might, the following avoids any
problem:

=IF(AND(F6="",G6""),"",SUM(H5,G6)-N(F6))

Are we doing homework now?