Function Mal-Functioning
 

I have this application that does a couple of things. One though, is being a
bit problematic. Can some one who has the answer chime in with corrections?
The problem is this, three columns
1.) Column I3 equal to =IF(ISBLANK(H17),"",IF(H17=4.5,0,$I$2-H17))

2.) Column J3 equal to =IF(ISBLANK(H17),"",IF(H17<=5.5,0,H17-$J$2))

3.) Column K3 equal to
=IF(AND(ISBLANK(I17),ISBLANK(J17)),"",IF(AND(I17=0 ,J17=0),"Pass","Fail"))

The problem is that even if the columns I & J are "" empty (No Value),
column H displays "Fail". What I need is to have no value shown in column "H"
if all columns to the left are "" (I & J are Blank).

Is it possible to correct this painlessly?

Looking forward to your input.

Thanks,

Jay,

I suspect you have formulas in I17 and J17 that are returning "", so they
are not really blank.

Try

=IF(AND(I17="",J17=""),"",IF(AND(I17=0,J17=0),"Pas s","Fail"))

HTH,
Bernie
MS Excel MVP


"Jay" wrote in message
...
I have this application that does a couple of things. One though, is being
a
bit problematic. Can some one who has the answer chime in with
corrections?
The problem is this, three columns
1.) Column I3 equal to =IF(ISBLANK(H17),"",IF(H17=4.5,0,$I$2-H17))

2.) Column J3 equal to =IF(ISBLANK(H17),"",IF(H17<=5.5,0,H17-$J$2))

3.) Column K3 equal to
=IF(AND(ISBLANK(I17),ISBLANK(J17)),"",IF(AND(I17=0 ,J17=0),"Pass","Fail"))

The problem is that even if the columns I & J are "" empty (No Value),
column H displays "Fail". What I need is to have no value shown in column
"H"
if all columns to the left are "" (I & J are Blank).

Is it possible to correct this painlessly?

Looking forward to your input.

Thanks,

Isblank is false if the cell contains anything, a formula that returns "" is
not blank
It could be that you need
=IF(AND(I17="",J17=""),"",IF(AND(I17=0,J17=0),"Pas s","Fail"))
Lance

"Jay" wrote:

I have this application that does a couple of things. One though, is being a
bit problematic. Can some one who has the answer chime in with corrections?
The problem is this, three columns
1.) Column I3 equal to =IF(ISBLANK(H17),"",IF(H17=4.5,0,$I$2-H17))

2.) Column J3 equal to =IF(ISBLANK(H17),"",IF(H17<=5.5,0,H17-$J$2))

3.) Column K3 equal to
=IF(AND(ISBLANK(I17),ISBLANK(J17)),"",IF(AND(I17=0 ,J17=0),"Pass","Fail"))

The problem is that even if the columns I & J are "" empty (No Value),
column H displays "Fail". What I need is to have no value shown in column "H"
if all columns to the left are "" (I & J are Blank).

Is it possible to correct this painlessly?

Looking forward to your input.

Thanks,

Maybe isnumber instead

--
Don Guillett
SalesAid Software

"Jay" wrote in message
...
I have this application that does a couple of things. One though, is being
a
bit problematic. Can some one who has the answer chime in with
corrections?
The problem is this, three columns
1.) Column I3 equal to =IF(ISBLANK(H17),"",IF(H17=4.5,0,$I$2-H17))

2.) Column J3 equal to =IF(ISBLANK(H17),"",IF(H17<=5.5,0,H17-$J$2))

3.) Column K3 equal to
=IF(AND(ISBLANK(I17),ISBLANK(J17)),"",IF(AND(I17=0 ,J17=0),"Pass","Fail"))

The problem is that even if the columns I & J are "" empty (No Value),
column H displays "Fail". What I need is to have no value shown in column
"H"
if all columns to the left are "" (I & J are Blank).

Is it possible to correct this painlessly?

Looking forward to your input.

Thanks,

A cell with a space or an empty string ("") is NOT BLANK

A cell with a formula in it WILL NEVER return TRUE when tested with ISBLANK()

If I17 and J17 have IF formulas in them that return "" for some results,
then test for that -

=IF(AND(I17="",J17="")

Personally, I don't much like using "" as one of the results for IF
statements because you don't know what's in that cell unless you navigate
there and view its contents in the edit bar. I'd prefer "No Match", or "No
Value". JMHO


"Jay" wrote:

I have this application that does a couple of things. One though, is being a
bit problematic. Can some one who has the answer chime in with corrections?
The problem is this, three columns
1.) Column I3 equal to =IF(ISBLANK(H17),"",IF(H17=4.5,0,$I$2-H17))

2.) Column J3 equal to =IF(ISBLANK(H17),"",IF(H17<=5.5,0,H17-$J$2))

3.) Column K3 equal to
=IF(AND(ISBLANK(I17),ISBLANK(J17)),"",IF(AND(I17=0 ,J17=0),"Pass","Fail"))

The problem is that even if the columns I & J are "" empty (No Value),
column H displays "Fail". What I need is to have no value shown in column "H"
if all columns to the left are "" (I & J are Blank).

Is it possible to correct this painlessly?

Looking forward to your input.

Thanks,

Isblank(cell) does not recognize cells with formulas as blanks. a workaround
is using (countblank(cell)=1) will give a TRUE when the cell is really blank
or a "" response. Cells showing as blank because of formating (eg ;;; and
the like) will will show as false.
"Jay" wrote:

I have this application that does a couple of things. One though, is being a
bit problematic. Can some one who has the answer chime in with corrections?
The problem is this, three columns
1.) Column I3 equal to =IF(ISBLANK(H17),"",IF(H17=4.5,0,$I$2-H17))

2.) Column J3 equal to =IF(ISBLANK(H17),"",IF(H17<=5.5,0,H17-$J$2))

3.) Column K3 equal to
=IF(AND(ISBLANK(I17),ISBLANK(J17)),"",IF(AND(I17=0 ,J17=0),"Pass","Fail"))

The problem is that even if the columns I & J are "" empty (No Value),
column H displays "Fail". What I need is to have no value shown in column "H"
if all columns to the left are "" (I & J are Blank).

Is it possible to correct this painlessly?

Looking forward to your input.

Thanks,